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

Dave F wrote...
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)

Perfect, thanks.

On Sep 20, 3:28 pm, Harlan Grove wrote:
Dave F wrote...
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)

maybe

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


"Dave F" wrote:

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