Need help with formula

[Russk68]

Hi All,
In cell D1 there is data that looks like this 2x100'. The leading digit will always start with a number between 1 & 8 and always in that format.
In Column A there are always just numbers.

In cell E1 (formula needed), I want to return the numbers from cells A1 & A2 (2 rows) indicated by the leading number in D1 (2) and combined them together separated by a comma.

Example: (ignore the ___...)

___A____B___C___D______E_____
1 123.................2x100'...123,205
2 205
3 321.................3x250'...321,504,217
4 504
5 217

So E3 returns the next 3 numbers down in column A because of the leading number (3) in D1

I hope this makes sense and Thank you!

 

[Eric W]

If you have a newer version of Excel (2016 or newer), this could work:

	A	B	C	D	E
2X100'
3X250'

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]123,205[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]321[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]321,504,217[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]504[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]217[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=IF(D1="","",TEXTJOIN(",",1,OFFSET(A1,0,0,LEFT(D1)+0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[bellman101]

I've not seen the TEXTJOIN() function. I'll have a look at that and see what it does as I have 16 now.

If you don't have 16 though, there's the good old fashioned string of IF statements!

=IF(D1="","",A1&IF(INT(LEFT(D1,1))>1,","&A2,"")&IF(INT(LEFT(D1,1))>2,","&A3,"")&IF(INT(LEFT(D1,1))>3,","&A4,"")&IF(INT(LEFT(D1,1))>4,","&A5,"")&IF(INT(LEFT(D1,1))>5,","&A6,"")&IF(INT(LEFT(D1,1))>6,","&A7,"")&IF(INT(LEFT(D1,1))>7,","&A8,"")&IF(INT(LEFT(D1,1))>8,","&A9,""))


Drop that into E1 and fill it down as needed.

Cheers
JB

 

[Russk68]

Works perfect!
Thank you very much!!

 

[bellman101]

You are welcome sir