The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

=====
How about an alternative?

Each time the Business Objects Query (whatever that is!) runs, you have a
program that copies that sick_mm-dd-yy.xls to Sick_Current.xls.

Then your formulas can always point to that sick_current.xls.

If the users have to go back to look at previous reports, they can use
edit|links|change source to point at one of the old workbooks.

TimN wrote:

I have created a userform where user enters a payroll beginning date, which
is always a Monday.
Based on the employee ID number, a VLOOKUP formula goes to a seperate
worksheet and finds the employee's available sick time. Employees earn
additional sick time every 2 week payroll period.
I have a Business Objects query that is scheduled to run every other Sunday
and saves to Excel the most current data (Sick time only shows up on the
Sunday prior to Monday that beginns a new payroll). It is named as
Sick_12-10-06.xls (Sick + system date).

I need a way to have my VLOOKUP know what file it needs to link to in order
to get the correct sick time. The key is the payroll beginning date the user
enters in the userform mentioned in the very first sentace above.

For example if the user enters payroll beginning date of 11/13/06, how do I
write a formula or VBA code that says "Whatever day is entered in the user
form for payroll beginning date, subtract 1 day and link to the file
containing that date, then perform the defined VLOOKUP function from that
file?"

Thanks for any suggestions.

--

Dave Peterson

Open the workbook with formulas that refer to other workbooks.

Then click on edit|links.

You'll see a bunch of options there--including changing the source (point to a
different "other" workbook).

TimN wrote:

I am not familiar with the indiredt() function. I will look into that.

Concerning your alternate idea, the user will very often have to go back to
an old file. What do you mean by edit links change source to point to an old
workbook?

"Dave Peterson" wrote:

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

=====
How about an alternative?

Each time the Business Objects Query (whatever that is!) runs, you have a
program that copies that sick_mm-dd-yy.xls to Sick_Current.xls.

Then your formulas can always point to that sick_current.xls.

If the users have to go back to look at previous reports, they can use
edit|links|change source to point at one of the old workbooks.

TimN wrote:

I have created a userform where user enters a payroll beginning date, which
is always a Monday.
Based on the employee ID number, a VLOOKUP formula goes to a seperate
worksheet and finds the employee's available sick time. Employees earn
additional sick time every 2 week payroll period.
I have a Business Objects query that is scheduled to run every other Sunday
and saves to Excel the most current data (Sick time only shows up on the
Sunday prior to Monday that beginns a new payroll). It is named as
Sick_12-10-06.xls (Sick + system date).

I need a way to have my VLOOKUP know what file it needs to link to in order
to get the correct sick time. The key is the payroll beginning date the user
enters in the userform mentioned in the very first sentace above.

For example if the user enters payroll beginning date of 11/13/06, how do I
write a formula or VBA code that says "Whatever day is entered in the user
form for payroll beginning date, subtract 1 day and link to the file
containing that date, then perform the defined VLOOKUP function from that
file?"

Thanks for any suggestions.

--

Dave Peterson


--

Dave Peterson