Need to indirectly reference columns >26 using A1 notation based on numeric column number- how to?

K

KR

A1 notation is the standard for our organization, so I'm looking for an
answer that maintains A1 notation.

I have a cell that contains a number (20 to 115 or higher). I need to pull a
value from the associated column.

So, I have a reference cell B1 that contains the number 27, and I need the
number in row 11.. I start my destination cell formula with:
=indirect("AA" & "11")

Now I need to replace the "AA" with a link to the source cell (B1,
containing the number 27), but transmute the number so that it returns
columns T through however many columns are used (AA, AF, BR, etc.). What is
the best way to do this if forced to maintain A1 notation?

=indirect(what_goes_here(B1) & "11")

Thanks!
Keith
 
B

Bob Phillips

=INDIRECT(LEFT(ADDRESS(ROW(),B1,4,TRUE),1+(B1>26))&"11")

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
K

KR

Thanks Bob!
I may use the offset option provided by another poster, but this is what I
had been attempting to do- no way I could have figured your formula out by
myself!
:)
Keith
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

formula converting number to column letter >26 5
convert column number to letter(s) 1
How to reference Cells AA and forward with CHAR (and a bonus quest 2
How to Control a formula by a character in adjacent column on same 4
How to use number in some cell as a part of other cells reference? 4
sum numbers in cell string array 6
How to count the maximum number of consecutives? 9
How to write a function for column A = product of two columns, B a 4

Top