I have 5 direct reports who have 5 of their own. I created a spreadsheet to
give to the 30 indirect reports and now I want to be able to gather the data
for each of my direct reports. I know I can have my direct report's
spreadsheet link to their reports but I don't want to create the templete 5
times and then redo it every time a direct or indirect report changes or one
is added.

Is there a way to link spreadsheets but instead of giving a definate
workbook name have it reference a cell within that workbook which would tell
it the name of the workbook to reference?

IE. We are always looking for cell b4, but (workbook_1XB4) and somewhere in
the workbook we are in it tells it what workbook_1 is.

Thanks

Bartman,

Use INDIRECT.

For example, if "Book1" were typed in cell A1, the following formula would
refer to cell B4 on tab "Sheet1" of workbook "Book1":

=INDIRECT("'[" & A1 & ".xls]Sheet1'!$B$4)

The extra bits (brackets and such) are just the formatting to get the
reference right.

Eric


"Bartman" wrote:

I have 5 direct reports who have 5 of their own. I created a spreadsheet to
give to the 30 indirect reports and now I want to be able to gather the data
for each of my direct reports. I know I can have my direct report's
spreadsheet link to their reports but I don't want to create the templete 5
times and then redo it every time a direct or indirect report changes or one
is added.

Is there a way to link spreadsheets but instead of giving a definate
workbook name have it reference a cell within that workbook which would tell
it the name of the workbook to reference?

IE. We are always looking for cell b4, but (workbook_1XB4) and somewhere in
the workbook we are in it tells it what workbook_1 is.

Thanks