Spreadsheet reference in formula?

[joja15]

I am trying to update the name of a spreadsheet used in a formula
through a cell reference or some tool that replaces the old name with
a new name. Hopefully someone can help me out with this problem.

I am using the following two spreadsheets:
Payroll - Lists name, current salary, benefits, etc
Budget - Uses data from Payroll spreadsheet and formulas in the budget
spreadsheet to forecast future expenses.

Here are the actual spreadsheet names for one month:

Payroll_01-01-08.xls
budget_01-01-08.xls

Right now my formula in the budget_01_01_08.xls spreadsheet looks like
the following:
=VLOOKUP($E2,'T:\Budgets\[Payroll.xls]Current Compensation'!$A$2:$K
$150,7,FALSE)

Each month when I get a new payroll spreadsheet I change the payroll
spreadsheet name from payroll_02_01_08.xls to payroll.xls so that it
will work with the formula each time.

What I would like to do though is have the spreadsheet name referenced
by a cell I calculate in the budget spreadsheet. for example:
=VLOOKUP($E2,'T:\Budgets\[$A$1]Current Compensation'!$A$2:$K
$150,7,FALSE)
Where $A$1 = payroll_01_01_08.xls or whatever the current date
spreadsheet is

or have an easy mechanism by which to replace the old payroll
spreadsheet name with the new payroll spreadsheet name each month. For
example change:
=VLOOKUP($E2,'T:\Budgets\[Payroll_01_01_08.xls]Current Compensation'!$A
$2:$K$150,7,FALSE) to:
=VLOOKUP($E2,'T:\Budgets\[Payroll_02_01_08.xls]Current Compensation'!$A
$2:$K$150,7,FALSE)

I have read about using the indirect.ext function and played around
with it but I ran into a problem with passwords on my files. Basically
for each line I had of the formula calculation it would ask for the
password on the spreadsheet it was referencing. Since I am doing this
formula for 100 people it became very tedious to enter the password
over and over.

Does anyone have any ideas on how I can do the above and not have the
problem of replacing the name of the spreadsheet in each line manually
or having to enter the password for the spreadsheet referenced for
each line the formula is in?

If there are any different ideas on how to do this from the way I am
trying to do it please let me know.

Thanks in advance for any help provided.

John

 

[JMB]

I don't think indirect can pull data from a closed workbook. And it is a
volatile function, so it could bog down your spreadsheet.

You could set up a named reference (let's say ExternalRange) to the external
book. Click Insert/Name/Define
ExternalRange ='T:\Budgets\[Payroll.xls]Current Compensation'!$A$2:$K$150

Then use
=VLOOKUP($E2,ExternalRange,7,FALSE)

When the next month rolls around, just change the definition for the
ExternalRange named reference.

I did a little testing and XL only asked for the password once when I
modified the named range reference.

I am trying to update the name of a spreadsheet used in a formula
through a cell reference or some tool that replaces the old name with
a new name. Hopefully someone can help me out with this problem.

I am using the following two spreadsheets:
Payroll - Lists name, current salary, benefits, etc
Budget - Uses data from Payroll spreadsheet and formulas in the budget
spreadsheet to forecast future expenses.

Here are the actual spreadsheet names for one month:

Payroll_01-01-08.xls
budget_01-01-08.xls

Right now my formula in the budget_01_01_08.xls spreadsheet looks like
the following:
=VLOOKUP($E2,'T:\Budgets\[Payroll.xls]Current Compensation'!$A$2:$K
$150,7,FALSE)

Each month when I get a new payroll spreadsheet I change the payroll
spreadsheet name from payroll_02_01_08.xls to payroll.xls so that it
will work with the formula each time.

What I would like to do though is have the spreadsheet name referenced
by a cell I calculate in the budget spreadsheet. for example:
=VLOOKUP($E2,'T:\Budgets\[$A$1]Current Compensation'!$A$2:$K
$150,7,FALSE)
Where $A$1 = payroll_01_01_08.xls or whatever the current date
spreadsheet is

or have an easy mechanism by which to replace the old payroll
spreadsheet name with the new payroll spreadsheet name each month. For
example change:
=VLOOKUP($E2,'T:\Budgets\[Payroll_01_01_08.xls]Current Compensation'!$A
$2:$K$150,7,FALSE) to:
=VLOOKUP($E2,'T:\Budgets\[Payroll_02_01_08.xls]Current Compensation'!$A
$2:$K$150,7,FALSE)

I have read about using the indirect.ext function and played around
with it but I ran into a problem with passwords on my files. Basically
for each line I had of the formula calculation it would ask for the
password on the spreadsheet it was referencing. Since I am doing this
formula for 100 people it became very tedious to enter the password
over and over.

Does anyone have any ideas on how I can do the above and not have the
problem of replacing the name of the spreadsheet in each line manually
or having to enter the password for the spreadsheet referenced for
each line the formula is in?

If there are any different ideas on how to do this from the way I am
trying to do it please let me know.

Thanks in advance for any help provided.

John

 

[joja15]

I don't know why I never thought about that. It worked perfectly!
Thank you for the quick response and the help.

- John