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

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

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

Hi Keith,

=OFFSET(A11,,B1-1)

--
Kind regards,

Niek Otten

"KR" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> The enclosed questions or comments are entirely mine and don't represent
> the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>

Doh.
<sheepish grin, slaps forehead>


"Niek Otten" <[email protected]> wrote in message
news:[email protected]...
> Hi Keith,
>
> =OFFSET(A11,,B1-1)
>
> --
> Kind regards,
>
> Niek Otten
>
> "KR" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > --
> > The enclosed questions or comments are entirely mine and don't represent
> > the
> > thoughts, views, or policy of my employer. Any errors or omissions are
my
> > own.
> >
> >
>
>

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

--

HTH

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


"KR" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> The enclosed questions or comments are entirely mine and don't represent
the
> thoughts, views, or policy of my employer. Any errors or omissions are my
> own.
>
>

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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
>
> =INDIRECT(LEFT(ADDRESS(ROW(),B1,4,TRUE),1+(B1>26))&"11")
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "KR" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > --
> > The enclosed questions or comments are entirely mine and don't represent
> the
> > thoughts, views, or policy of my employer. Any errors or omissions are
my
> > own.
> >
> >
>
>

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

!!

=INDEX(11:11,B1)