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.