Comparing two daily files by changing the cell address
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 |
Comparing two daily files by changing the cell address
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 |
Comparing two daily files by changing the cell address
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 |
Comparing two daily files by changing the cell address
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 |
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 |
Comparing two daily files by changing the cell address
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. Fanny wrote: 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 |
All times are GMT +1. The time now is 05:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com