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

  • Thread starter Thread starter Dave F
  • Start date Start date
D

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
 
Dave F said:
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
Click to expand...
....

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.

...

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)
Click to expand...
 
Back
Top