| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparing Full Files | Excel Discussion (Misc queries) | |||
| Comparing a value in EXCEL and XML files | Excel Discussion (Misc queries) | |||
| Comparing-Merging Two Files | Excel Discussion (Misc queries) | |||
| changing word files to excel files | Excel Discussion (Misc queries) | |||
| Comparing value in two different files | Excel Discussion (Misc queries) | |||
Linear Mode
