[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Re: Vlookup problem

Dave Peterson

12/13/2006 5:46:00 PM

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://xcell0...

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
2 Answers

Dave Peterson

12/13/2006 8:15:00 PM

0

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://xcell0...
> >
> > 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

TimN

12/13/2006 8:58:00 PM

0

Dave,
Sorry, I should have thought about that a little more. I know exactly what
you are talking about. That may be the solution I go with. I hate to say,
but the users are very unsophisticated, so I'm not sure if I can live with
this or not. I was hoping for some systematic way that when the user typed
in the payroll period date, behind the scenes, that triggered a VBA code to
subtract one day and go to the directory where the files are saved and look
for the file containing that date. I will give this a go however, and see
what the response is.

Thanks!


"Dave Peterson" wrote:

> 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://xcell0...
> > >
> > > 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
>