Deleting a worksheet but retaining values from the worksheet.
 

Hi all, I have a problem that will probably be deceivingly easy or
suprisingly difficult to fix...

In a workbook, where "Sheet 1" has formulas like =5*Sheet2!A1 and
=5*Sheet3!B2

I would like to be able to delete "Sheet 2" so that anywhere in "Sheet
1" where a formula referenced "Sheet 2", the reference is replaced with
the value - while leaving formulas for still-present worksheets (like
"Sheet 3").

For example, if "Sheet2!A1" = 5 and "Sheet3!B2" = 10 and then I delete
"Sheet 2", the "Sheet 1" formulas I mentioned above would do this:

**"Sheet 1" before deleting "Sheet 2"
In one "Sheet 1" cell: =5*Sheet2!A1
In another "Sheet 1" cell: =5*Sheet3!B2

**"Sheet 1" after deleting "Sheet 2"
In one "Sheet 1" cell: =5*5
In another "Sheet 1" cell: =5*Sheet3!B2

So I can't use "Paste Values" because it would paste the values for all
the formulas. I just want Excel to replace the no-longer-valid-links
after deleting a worksheet with the values it last pulled in.

Has Excel got a function to do this? Or does anyone know a clever way
to do it?

Thanks in advance!

Nick

Deleting a worksheet but retaining values from the worksheet.
 

Check this out:
http://groups.google.com/group/micro...1b1829291a18de

Dave
--
Brevity is the soul of wit.


" wrote:

Hi all, I have a problem that will probably be deceivingly easy or
suprisingly difficult to fix...

In a workbook, where "Sheet 1" has formulas like =5*Sheet2!A1 and
=5*Sheet3!B2

I would like to be able to delete "Sheet 2" so that anywhere in "Sheet
1" where a formula referenced "Sheet 2", the reference is replaced with
the value - while leaving formulas for still-present worksheets (like
"Sheet 3").

For example, if "Sheet2!A1" = 5 and "Sheet3!B2" = 10 and then I delete
"Sheet 2", the "Sheet 1" formulas I mentioned above would do this:

**"Sheet 1" before deleting "Sheet 2"
In one "Sheet 1" cell: =5*Sheet2!A1
In another "Sheet 1" cell: =5*Sheet3!B2

**"Sheet 1" after deleting "Sheet 2"
In one "Sheet 1" cell: =5*5
In another "Sheet 1" cell: =5*Sheet3!B2

So I can't use "Paste Values" because it would paste the values for all
the formulas. I just want Excel to replace the no-longer-valid-links
after deleting a worksheet with the values it last pulled in.

Has Excel got a function to do this? Or does anyone know a clever way
to do it?

Thanks in advance!

Nick