Indirect with COLUMN
 

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

Indirect with COLUMN
 

=CHAR(COLUMN()+64)

returns letter for active column


"JEFF" skrev:

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

Indirect with COLUMN
 

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

"excelent" wrote:

=CHAR(COLUMN()+64)

returns letter for active column


"JEFF" skrev:

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

Indirect with COLUMN
 

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JEFF" wrote in message
...
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()))

Indirect with COLUMN
 

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

=CHAR(65) = A


"JEFF" skrev:

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

"excelent" wrote:

=CHAR(COLUMN()+64)

returns letter for active column


"JEFF" skrev:

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

Indirect with COLUMN
 

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

"Ragdyer" wrote:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JEFF" wrote in message
...
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()))

Indirect with COLUMN
 

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


"JEFF" wrote:

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

Indirect with COLUMN
 

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JEFF" wrote in message
...
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

"Ragdyer" wrote:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JEFF" wrote in message
...
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()))

Indirect with COLUMN
 

perfect.... thanks.

"Ragdyer" wrote:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JEFF" wrote in message
...
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

"Ragdyer" wrote:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JEFF" wrote in message
...
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()))

Indirect with COLUMN
 

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

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JEFF" wrote in message
...
perfect.... thanks.

"Ragdyer" wrote:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JEFF" wrote in message
...
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

"Ragdyer" wrote:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"JEFF" wrote in message
...
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()))