I have a formula in cell G2 of a worksheet called "Departmental Summary"
Column A of the same workbook contains employee names and there is a separate
worksheet in the file for each employee, with the name of the worksheet being
the exact same as the value for the employee in column A.

The formula in G2 is:

=IF(COLUMN(A2)MAX('SheetName'!$T:$T),"",INDEX('Sh eetName'!$R:$R,MATCH(COLUMN(A2),'SheetName'!$T:$T, 0)))

I would like to replace the SheetName with the Indirect function pointing to
cell A2 in the Departmental Summary worksheet, but I can't seem to get it to
work.

Can anyone help me out ?

Thanks !

Everywhere you have SheetName replace it with this (using the appropriate
column refs):

INDIRECT("'"&'Departmental Summary'!A2&"'!T:T")

--
Biff
Microsoft Excel MVP


"Eric_in_EVV" wrote in message
...
I have a formula in cell G2 of a worksheet called "Departmental Summary"
Column A of the same workbook contains employee names and there is a
separate
worksheet in the file for each employee, with the name of the worksheet
being
the exact same as the value for the employee in column A.

The formula in G2 is:

=IF(COLUMN(A2)MAX('SheetName'!$T:$T),"",INDEX('Sh eetName'!$R:$R,MATCH(COLUMN(A2),'SheetName'!$T:$T, 0)))

I would like to replace the SheetName with the Indirect function pointing
to
cell A2 in the Departmental Summary worksheet, but I can't seem to get it
to
work.

Can anyone help me out ?

Thanks !

I guess I wasn't very clear....Departmental Summary is the sheet where this
formula resides. The sheets named with the values in column A of
Departmental Summary are the ones where the Indirect formula needs to point.
In other words, Cell G2 of Departmental Summary needs to have the Indirect
function pointing to cell A1 of Departmental Summary as the sheet name used
in the Max , Index and Match functions. Does that make it any clearer as to
what I am trying to get working ?

Thanks !

"T. Valko" wrote:

Everywhere you have SheetName replace it with this (using the appropriate
column refs):

INDIRECT("'"&'Departmental Summary'!A2&"'!T:T")

--
Biff
Microsoft Excel MVP


"Eric_in_EVV" wrote in message
...
I have a formula in cell G2 of a worksheet called "Departmental Summary"
Column A of the same workbook contains employee names and there is a
separate
worksheet in the file for each employee, with the name of the worksheet
being
the exact same as the value for the employee in column A.

The formula in G2 is:

=IF(COLUMN(A2)MAX('SheetName'!$T:$T),"",INDEX('Sh eetName'!$R:$R,MATCH(COLUMN(A2),'SheetName'!$T:$T, 0)))

I would like to replace the SheetName with the Indirect function pointing
to
cell A2 in the Departmental Summary worksheet, but I can't seem to get it
to
work.

Can anyone help me out ?

Thanks !


.

Then just remove the sheet name (although it should work with it included):

A1 = some sheet name = Sales July 09

=MAX(INDIRECT("'"&A1&"'!T:T"))

Evaluates to:

=MAX('Sales July 09'!$T:$T)

--
Biff
Microsoft Excel MVP


"Eric_in_EVV" wrote in message
...
I guess I wasn't very clear....Departmental Summary is the sheet where this
formula resides. The sheets named with the values in column A of
Departmental Summary are the ones where the Indirect formula needs to
point.
In other words, Cell G2 of Departmental Summary needs to have the Indirect
function pointing to cell A1 of Departmental Summary as the sheet name
used
in the Max , Index and Match functions. Does that make it any clearer as
to
what I am trying to get working ?

Thanks !

"T. Valko" wrote:

Everywhere you have SheetName replace it with this (using the appropriate
column refs):

INDIRECT("'"&'Departmental Summary'!A2&"'!T:T")

--
Biff
Microsoft Excel MVP


"Eric_in_EVV" wrote in message
...
I have a formula in cell G2 of a worksheet called "Departmental Summary"
Column A of the same workbook contains employee names and there is a
separate
worksheet in the file for each employee, with the name of the worksheet
being
the exact same as the value for the employee in column A.

The formula in G2 is:

=IF(COLUMN(A2)MAX('SheetName'!$T:$T),"",INDEX('Sh eetName'!$R:$R,MATCH(COLUMN(A2),'SheetName'!$T:$T, 0)))

I would like to replace the SheetName with the Indirect function
pointing
to
cell A2 in the Departmental Summary worksheet, but I can't seem to get
it
to
work.

Can anyone help me out ?

Thanks !


.

Thanks Biff - worked like a charm.

I'm now sitting here saying, "Well $%&# ! It was that simple ?!?!"

Thanks again for the assist ! Much appreciated !

"T. Valko" wrote:

Then just remove the sheet name (although it should work with it included):

A1 = some sheet name = Sales July 09

=MAX(INDIRECT("'"&A1&"'!T:T"))

Evaluates to:

=MAX('Sales July 09'!$T:$T)

--
Biff
Microsoft Excel MVP


"Eric_in_EVV" wrote in message
...
I guess I wasn't very clear....Departmental Summary is the sheet where this
formula resides. The sheets named with the values in column A of
Departmental Summary are the ones where the Indirect formula needs to
point.
In other words, Cell G2 of Departmental Summary needs to have the Indirect
function pointing to cell A1 of Departmental Summary as the sheet name
used
in the Max , Index and Match functions. Does that make it any clearer as
to
what I am trying to get working ?

Thanks !

"T. Valko" wrote:

Everywhere you have SheetName replace it with this (using the appropriate
column refs):

INDIRECT("'"&'Departmental Summary'!A2&"'!T:T")

--
Biff
Microsoft Excel MVP


"Eric_in_EVV" wrote in message
...
I have a formula in cell G2 of a worksheet called "Departmental Summary"
Column A of the same workbook contains employee names and there is a
separate
worksheet in the file for each employee, with the name of the worksheet
being
the exact same as the value for the employee in column A.

The formula in G2 is:

=IF(COLUMN(A2)MAX('SheetName'!$T:$T),"",INDEX('Sh eetName'!$R:$R,MATCH(COLUMN(A2),'SheetName'!$T:$T, 0)))

I would like to replace the SheetName with the Indirect function
pointing
to
cell A2 in the Departmental Summary worksheet, but I can't seem to get
it
to
work.

Can anyone help me out ?

Thanks !


.



.

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Eric_in_EVV" wrote in message
...
Thanks Biff - worked like a charm.

I'm now sitting here saying, "Well $%&# ! It was that simple ?!?!"

Thanks again for the assist ! Much appreciated !

"T. Valko" wrote:

Then just remove the sheet name (although it should work with it
included):

A1 = some sheet name = Sales July 09

=MAX(INDIRECT("'"&A1&"'!T:T"))

Evaluates to:

=MAX('Sales July 09'!$T:$T)

--
Biff
Microsoft Excel MVP


"Eric_in_EVV" wrote in message
...
I guess I wasn't very clear....Departmental Summary is the sheet where
this
formula resides. The sheets named with the values in column A of
Departmental Summary are the ones where the Indirect formula needs to
point.
In other words, Cell G2 of Departmental Summary needs to have the
Indirect
function pointing to cell A1 of Departmental Summary as the sheet name
used
in the Max , Index and Match functions. Does that make it any clearer
as
to
what I am trying to get working ?

Thanks !

"T. Valko" wrote:

Everywhere you have SheetName replace it with this (using the
appropriate
column refs):

INDIRECT("'"&'Departmental Summary'!A2&"'!T:T")

--
Biff
Microsoft Excel MVP


"Eric_in_EVV" wrote in message
...
I have a formula in cell G2 of a worksheet called "Departmental
Summary"
Column A of the same workbook contains employee names and there is a
separate
worksheet in the file for each employee, with the name of the
worksheet
being
the exact same as the value for the employee in column A.

The formula in G2 is:

=IF(COLUMN(A2)MAX('SheetName'!$T:$T),"",INDEX('Sh eetName'!$R:$R,MATCH(COLUMN(A2),'SheetName'!$T:$T, 0)))

I would like to replace the SheetName with the Indirect function
pointing
to
cell A2 in the Departmental Summary worksheet, but I can't seem to
get
it
to
work.

Can anyone help me out ?

Thanks !


.



.