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
1	123			2X100'	123,205
2	205
3	321			3X250'	321,504,217
4	504
5	217

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula E1 =IF(D1="","",TEXTJOIN(",",1,OFFSET(A1,0,0,LEFT(D1)+0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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