Formula uses previous saved spreadsheet data!

N

Nicholas1

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.
 
N

Nicholas1

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 said:
Highlight all desired data and use Edit>Replace.

Nicholas1 said:
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.
Click to expand...
Click to expand...
 
S

Sean Timmons

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 said:
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 said:
Highlight all desired data and use Edit>Replace.

Nicholas1 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
G

Gord Dibben

So set find>options to look in formulas.


Gord Dibben MS Excel MVP

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 said:
Highlight all desired data and use Edit>Replace.

Nicholas1 said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reference previous worksheet within VLOOKUP 2
Spreadsheet reference in formula? 2
Split Data Series (Lookup, Index, Match references) 1
Cross Tab Query 4
Excel problems 0
Formula to match a data point in 2 wrkshts 5
Financial Spreadsheet Help 2
Formula checking multiple worksheets 1

Top