INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4)

[Dave F]

How would I make this formula work:

INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4)

A1 has F2 in it
A2 has Z8 in it


Thanks. Is this even possible? COLUMNS(INDIRECT(A1):INDIRECT(A2))
resolves to 21; my thought was then that I could create the range
A21:A30

I get a #VALUE! error when I try the above.

Thoughts?

Dave

 

[Harlan Grove]

How would I make this formula work:

INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4)

A1 has F2 in it
A2 has Z8 in it

....

You need another level of INDIRECT calls, but you could eliminate one
of the existing INDIRECT calls. Try

=INDEX(INDIRECT("A"&COLUMNS(INDIRECT(A1&":"&A2))):A30,4)

However, this would just pull the value of the cell 3 rows below the
topmost cell in the resulting range. Given your sample values for A1
and A2, the COLUMNS call would return 21, making your formula
equivalent to

=INDEX(A21:A30,4)

and the result would be the value of cell A24. You could achieve the
same result with either

=OFFSET(A1,COLUMNS(INDIRECT(A1&":"&A2))+2,0)

or

=INDIRECT("A"&COLUMNS(INDIRECT(A1&":"&A2))+3)

 

[Dave F]

Perfect, thanks.

...

You need another level of INDIRECT calls, but you could eliminate one
of the existing INDIRECT calls. Try

=INDEX(INDIRECT("A"&COLUMNS(INDIRECT(A1&":"&A2))):A30,4)

However, this would just pull the value of the cell 3 rows below the
topmost cell in the resulting range. Given your sample values for A1
and A2, the COLUMNS call would return 21, making your formula
equivalent to

=INDEX(A21:A30,4)

and the result would be the value of cell A24. You could achieve the
same result with either

=OFFSET(A1,COLUMNS(INDIRECT(A1&":"&A2))+2,0)

or

=INDIRECT("A"&COLUMNS(INDIRECT(A1&":"&A2))+3)

 

[Guest]

maybe

=INDEX(INDIRECT("a"&COLUMNS(INDIRECT(A1&":"&A2))&":A30"),4)