trying to use Indirect function

[Eric_in_EVV]

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('SheetName'!$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 !

 

[T. Valko]

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

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

 

[Eric_in_EVV]

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]

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)

 

[Eric_in_EVV]

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]

Good deal. Thanks for the feedback!