Hello,

In my formula to take the amount of total collection there is a file name
ExtractDatainvcoll_Servr_080718, identified by date 080718 means this is the
status as of 18 July 2008.

But for today Jul 31, 2008 I should change the formula to reflect the
correct Vlookup, to be the name of ExtractDatainvcoll_Servr_080731.

My question is how can we make it automatically ( instead of manually
changed) it will change from the last file name.

Below is my formula:


=IF(ISNA(VLOOKUP(A13,[ExtractDatainvcoll_Servr_080718.xls]Sheet1!$A$13:$H$600,8,FALSE)),0,VLOOKUP(A13,[ExtractDatainvcoll_Servr_080718.xls]Sheet1!$A$13:$H$600,8,FALSE))

Thanks in advance for any help.
--
H. Frank Situmorang

If you have a lot of those formulae, then you can change them in one
operation by highlighting the cells with the formula in, then CTRL-H
(or Edit | Replace), and:

Find What: 080718
Replace with: 080731
Click Replace All

Another more automatic way would be to calculate the appropriate date
string from today's date and to use INDIRECT in your formula, but that
will only work if the file is open at the same time.

Hope this helps.

Pete

On Jul 31, 3:43*am, Frank Situmorang wrote:
Hello,

In my formula to take the amount of total collection there is a file name
ExtractDatainvcoll_Servr_080718, identified by date 080718 means this is the
status as of 18 July 2008.

But for today Jul 31, 2008 I should change the formula to reflect the
correct Vlookup, to be the name of ExtractDatainvcoll_Servr_080731.

My question is how can we make it automatically ( instead of manually
changed) it will change from the last file name.

Below is my formula:

=IF(ISNA(VLOOKUP(A13,[ExtractDatainvcoll_Servr_080718.xls]Sheet1!$A$13:$H$6*00,8,FALSE)),0,VLOOKUP(A13,[ExtractDatainvcoll_Servr_080718.xls]Sheet1!$A$1*3:$H$600,8,FALSE))

Thanks in advance for any help.
--
H. Frank Situmorang

Thanks Pete for your response, what if it is in one cell, as you see my
formula is a bit long, it is still better to use Cntl_H, then we copy it down.

But when I tried it it didn't work, should we higlight all the text in the
formula?, and then do we need to change the option?.

I am also interested in the 2ndone. It does not matter if I open them at the
same time, but how can we use INDIRECT formula.

Thanks in advance for your help.
--
H. Frank Situmorang


"Frank Situmorang" wrote:

Hello,

In my formula to take the amount of total collection there is a file name
ExtractDatainvcoll_Servr_080718, identified by date 080718 means this is the
status as of 18 July 2008.

But for today Jul 31, 2008 I should change the formula to reflect the
correct Vlookup, to be the name of ExtractDatainvcoll_Servr_080731.

My question is how can we make it automatically ( instead of manually
changed) it will change from the last file name.

Below is my formula:


=IF(ISNA(VLOOKUP(A13,[ExtractDatainvcoll_Servr_080718.xls]Sheet1!$A$13:$H$600,8,FALSE)),0,VLOOKUP(A13,[ExtractDatainvcoll_Servr_080718.xls]Sheet1!$A$13:$H$600,8,FALSE))

Thanks in advance for any help.
--
H. Frank Situmorang