Indirect with COLUMN

[Guest]

Hello,

Trying to use the INDIRECT function to allow me to use a drop list, and
based on my choice, I bring the data from a similarly name worksheet. This
data resides in the same place on all worksheets. In the formula below, I
have the worksheet name and the column and row. You can see I'm struggling
with getting the column to return as a letter, rather than a number.... Is
there a better way than having to manually enter the column reference?
Thanks.

=INDIRECT($AM$1&"!$ao"&(ROW()))

 

[Guest]

=CHAR(COLUMN()+64)

returns letter for active column


"JEFF" skrev:

 

[Guest]

Sorry, but I'm not returning the accurate column (I'm in Column AX and
returning "R")

 

[Ragdyer]

Don't know if I follow you exactly.

This will start in ColumnA and Row1, and will increment as you drag it
across and / or down:

=INDIRECT($AM$1&"!"&ADDRESS(ROWS($1:1),COLUMNS($A:A)))

 

[Guest]

=CHAR(COLUMN()+64-32) = R

=CHAR(65) = A


"JEFF" skrev:

 

[Guest]

Sorry, let me keep it simple: I just want to have a master worksheet that
uses a picklist (validation) that populates based on data found in other
worksheets. Those worksheets are named the same as the picklist... So if I
pick "Apple", the data in cell A1 of the Master will come from Apple!A1

Hope that helps

 

[Guest]

This formula will return the letter value of whatever column it is placed in.
Is this something you can work with?

=IF(COLUMN()>26,CHAR(INT((COLUMN()-1)/26)+64)&CHAR(MOD(COLUMN()+25,26)+65),CHAR(COLUMN()+64))

HTH,
Elkar

 

[Ragdyer]

This will return the contents of the *exact* cell that you enter it into ...
BUT ...from the sheet name entered in AM1:

=INDIRECT($AM$1&"!"&ADDRESS(ROW(),COLUMN()))

 

[Guest]

perfect.... thanks.

This will return the contents of the *exact* cell that you enter it into ...
BUT ...from the sheet name entered in AM1:

=INDIRECT($AM$1&"!"&ADDRESS(ROW(),COLUMN()))

 

[Ragdyer]

Appreciate the feed-back.

BTW ... that formula will *only* work for sheet names that have *no* spaces
in them.

This is more robust, and can be used for *any* sheet name configuration:

=INDIRECT("'"&$AM$1&"'!"&ADDRESS(ROW(),COLUMN()))