Indirect with COLUMN

J

JEFF

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()))
 
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)))
 
J

JEFF

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
 
E

Elkar

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
 
R

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()))
 
J

JEFF

perfect.... thanks.

Ragdyer said:
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()))
Click to expand...
 
R

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()))
 
Top