keep formula referencing sheet when sheet is removed

Hey all, I'm stuck on an issue here.
I have a report macro that is run after importing data from a website.
When that report runs it creates multiple sheets, one of which is a formulas sheet.
The formula's that are in the sheet are referencing a second sheet in the same workbook.
Eg: Formula in sheet1; cell F2: =COUNTIF(Wireless!F:F,Wireless!F2)>1. references Sheet2; Cell F2.

If sheet2 is deleted then the formula in Sheet1 changes to: =COUNTIF(#REF!F:F,#REF!F2)>1.

Is there a way I can avoid this.

I have tried locking the cells and the sheet but the formula result does not update as a result of this.

Cheers

Hi
Locking is not a solution. Depending on the number of cells with formula, you can copy and pastespecial-values only (overwrite) before deleting sheet2. you can also use macro.

Please Login or Register  to view this content.

Ravi

Thanks for that Ravi. \
I am using a macro at the moment, I was just hoping there was a way to avoid having to write the formulas page every time the macro is run as it is a bit time consuming.
I was looking to have the formula's pre written into the master book but hidden so no one could touch them.

Cheers anyway.

Bill

Thanks SHG,
Tried that and it does not reference the cell correctly. Gives me true when should be false??

Cheers for that anyway.

Bill