Comparing two daily files by changing the cell address

[Guest]

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

 

[Roger Govier]

Hi Fanny

Try
=INDIRECT("'C:\[PL"&"text(B1,"ddmm")"&".xls]Sheet1'!$B$2")

 

[Guest]

Hi Mr Roger,

I tried the formula but it does not work. thanks.

Fanny

Hi Fanny

Try
=INDIRECT("'C:\[PL"&"text(B1,"ddmm")"&".xls]Sheet1'!$B$2")


--
Regards

Roger Govier

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

 

[Roger Govier]

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

Hi Mr Roger,

I tried the formula but it does not work. thanks.

Fanny

Hi Fanny

Try
=INDIRECT("'C:\[PL"&"text(B1,"ddmm")"&".xls]Sheet1'!$B$2")


--
Regards

Roger Govier

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

 

[Guest]

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

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

Hi Mr Roger,

I tried the formula but it does not work. thanks.

Fanny

Hi Fanny

Try
=INDIRECT("'C:\[PL"&"text(B1,"ddmm")"&".xls]Sheet1'!$B$2")


--
Regards

Roger Govier


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

 

[Dave Peterson]

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.

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