Comparing two daily files by changing the cell address
Dear Mr Roger,
Thank you a lot. I got the answer when I open the source files at the same
time. If I close all the files, all the formulas becomes #REF!. However, is
it possible not to open the source files.
regards,
Fanny
"Roger Govier" wrote:
Hi Fanny
My apologies, slipped some un-needed quotes in there.
Try
=INDIRECT("'C:\[PL"&TEXT(B1,"ddmm")&".xls]Sheet1'!$B$2")
--
Regards
Roger Govier
"Fanny" wrote in message
...
Hi Mr Roger,
I tried the formula but it does not work. thanks.
Fanny
"Roger Govier" wrote:
Hi Fanny
Try
=INDIRECT("'C:\[PL"&"text(B1,"ddmm")"&".xls]Sheet1'!$B$2")
--
Regards
Roger Govier
"Fanny" wrote in message
...
Hi,
I need to compare the P/L daily. I save the each daily file as
PL0102.xls,
PL0102.xls and so on.
Date 01/02/2007
02/02/2007
Difference
(B1)
Income 1000
1200
+200
Formulas ('C:\[PL0102.xls]Sheet1'!$B$2)
('C:\[PL0202.xls]Sheet1'!$B$8)
Expenses 500
800
+300
My method is change the dates manually and use "Replace" to change
the
file
names in each column. I had tried to save the step of "Replace" by
using the
formula below but not successful and it comes up with #REF!.
=('C:\[PL)&(text(B1,"ddmm")&(.xls]Sheet1'!$B$2)
Can anyone help? Thanks
Fanny
|