| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula uses previous saved spreadsheet data!
Basically I produce a report on forecasts ever month. i created the sheet oh
the 28th and saved as New 'Forecast report - 28.09.09' for the time bring. Today I wante to update it with the latest data but the Formulas are using the previous reports sheet with the pivot table (entitled 'Pivot'). how do i get rid of this connection? The Formula is: =IFERROR(IF(F2="Forecast based on inv data",IF(VLOOKUP(A2,'[New Forecast report - 28 09 09.xlsx]Pivot'!$C$6:$M$1048576,4,FALSE)=0,"",VLOOKUP(A2,'[New Forecast report - 28 09 09.xlsx]Pivot'!$C$6:$M$1048576,4,FALSE)),IF(VLOOKUP(A1,'[New Forecast report - 28 09 09.xlsx]Forecasted Items'!$A$5:$T$1048576,3,FALSE)=0,"",VLOOKUP(A1,'[New Forecast report - 28 09 09.xlsx]Forecasted Items'!$A$5:$T$1048576,3,FALSE))),"") I would delete the 'connection address' but theres 12 columns for every month and it takes time manually doing this. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula uses previous saved spreadsheet data!
Highlight all desired data and use EditReplace.
"Nicholas1" wrote: Basically I produce a report on forecasts ever month. i created the sheet oh the 28th and saved as New 'Forecast report - 28.09.09' for the time bring. Today I wante to update it with the latest data but the Formulas are using the previous reports sheet with the pivot table (entitled 'Pivot'). how do i get rid of this connection? The Formula is: =IFERROR(IF(F2="Forecast based on inv data",IF(VLOOKUP(A2,'[New Forecast report - 28 09 09.xlsx]Pivot'!$C$6:$M$1048576,4,FALSE)=0,"",VLOOKUP(A2,'[New Forecast report - 28 09 09.xlsx]Pivot'!$C$6:$M$1048576,4,FALSE)),IF(VLOOKUP(A1,'[New Forecast report - 28 09 09.xlsx]Forecasted Items'!$A$5:$T$1048576,3,FALSE)=0,"",VLOOKUP(A1,'[New Forecast report - 28 09 09.xlsx]Forecasted Items'!$A$5:$T$1048576,3,FALSE))),"") I would delete the 'connection address' but theres 12 columns for every month and it takes time manually doing this. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula uses previous saved spreadsheet data!
It doesn't work, the [New Forecast report - 28 09 09.xlsx] is in the formula
so the Find/replace function doesn't register it. "Sean Timmons" wrote: Highlight all desired data and use EditReplace. "Nicholas1" wrote: Basically I produce a report on forecasts ever month. i created the sheet oh the 28th and saved as New 'Forecast report - 28.09.09' for the time bring. Today I wante to update it with the latest data but the Formulas are using the previous reports sheet with the pivot table (entitled 'Pivot'). how do i get rid of this connection? The Formula is: =IFERROR(IF(F2="Forecast based on inv data",IF(VLOOKUP(A2,'[New Forecast report - 28 09 09.xlsx]Pivot'!$C$6:$M$1048576,4,FALSE)=0,"",VLOOKUP(A2,'[New Forecast report - 28 09 09.xlsx]Pivot'!$C$6:$M$1048576,4,FALSE)),IF(VLOOKUP(A1,'[New Forecast report - 28 09 09.xlsx]Forecasted Items'!$A$5:$T$1048576,3,FALSE)=0,"",VLOOKUP(A1,'[New Forecast report - 28 09 09.xlsx]Forecasted Items'!$A$5:$T$1048576,3,FALSE))),"") I would delete the 'connection address' but theres 12 columns for every month and it takes time manually doing this. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula uses previous saved spreadsheet data!
Making sure I udnerstand what you're trying to do. You want to change the
source workbook name in the formula from [New Forecast report - 28 09 09.xlsx] to another file name... Replace will fidn values in formulas and update them, so if yoru new file name was, say, [New Forecast report - 28 10 09.xlsx] , you'd just have to have Find: [New Forecast report - 28 09 09.xlsx] Replace With: [New Forecast report - 28 10 09.xlsx] And Replace All. Just need to make sure you don't have some other section of your worksheet highlighted when you perform this action (i.e. - Column A highlighted when your links are in column D). "Nicholas1" wrote: It doesn't work, the [New Forecast report - 28 09 09.xlsx] is in the formula so the Find/replace function doesn't register it. "Sean Timmons" wrote: Highlight all desired data and use EditReplace. "Nicholas1" wrote: Basically I produce a report on forecasts ever month. i created the sheet oh the 28th and saved as New 'Forecast report - 28.09.09' for the time bring. Today I wante to update it with the latest data but the Formulas are using the previous reports sheet with the pivot table (entitled 'Pivot'). how do i get rid of this connection? The Formula is: =IFERROR(IF(F2="Forecast based on inv data",IF(VLOOKUP(A2,'[New Forecast report - 28 09 09.xlsx]Pivot'!$C$6:$M$1048576,4,FALSE)=0,"",VLOOKUP(A2,'[New Forecast report - 28 09 09.xlsx]Pivot'!$C$6:$M$1048576,4,FALSE)),IF(VLOOKUP(A1,'[New Forecast report - 28 09 09.xlsx]Forecasted Items'!$A$5:$T$1048576,3,FALSE)=0,"",VLOOKUP(A1,'[New Forecast report - 28 09 09.xlsx]Forecasted Items'!$A$5:$T$1048576,3,FALSE))),"") I would delete the 'connection address' but theres 12 columns for every month and it takes time manually doing this. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula uses previous saved spreadsheet data!
So set findoptions to look in formulas.
Gord Dibben MS Excel MVP On Fri, 2 Oct 2009 02:06:01 -0700, Nicholas1 wrote: It doesn't work, the [New Forecast report - 28 09 09.xlsx] is in the formula so the Find/replace function doesn't register it. "Sean Timmons" wrote: Highlight all desired data and use EditReplace. "Nicholas1" wrote: Basically I produce a report on forecasts ever month. i created the sheet oh the 28th and saved as New 'Forecast report - 28.09.09' for the time bring. Today I wante to update it with the latest data but the Formulas are using the previous reports sheet with the pivot table (entitled 'Pivot'). how do i get rid of this connection? The Formula is: =IFERROR(IF(F2="Forecast based on inv data",IF(VLOOKUP(A2,'[New Forecast report - 28 09 09.xlsx]Pivot'!$C$6:$M$1048576,4,FALSE)=0,"",VLOOKUP(A2,'[New Forecast report - 28 09 09.xlsx]Pivot'!$C$6:$M$1048576,4,FALSE)),IF(VLOOKUP(A1,'[New Forecast report - 28 09 09.xlsx]Forecasted Items'!$A$5:$T$1048576,3,FALSE)=0,"",VLOOKUP(A1,'[New Forecast report - 28 09 09.xlsx]Forecasted Items'!$A$5:$T$1048576,3,FALSE))),"") I would delete the 'connection address' but theres 12 columns for every month and it takes time manually doing this. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HOW DO I KEEP DATA (SPREADSHEET EXCEL) FROM SHRINKING WHEN SAVED? | Excel Discussion (Misc queries) | |||
| How do I restore a previous version of a file I changed and saved | Excel Discussion (Misc queries) | |||
| entering new data in a saved spreadsheet without losing formulas? | New Users to Excel | |||
| Can I access the previous version of a saved file? | Excel Discussion (Misc queries) | |||
| How can I open previous version of saved file in Excel? | Excel Discussion (Misc queries) | |||
Hybrid Mode
