Replacing worksheets, but I get #ref!

[Cage]

Hello,

I have workbook which has two worksheets A and B. SheetA contains data, whilst sheet B links to sheet A. However every month sheet A needs updating, so I delete it and replace it. The problem is when I do this, the links on sheet B have a #Ref! error. It is possible to keep the link static regardless of whether I delete the linked sheet?

TIA

Cage

 

[fredericofonseca]

Don't delete the worksheet.

Just clear it's contents

If you wish to delete the worksheet then have that on another workbook, and be sure to delete it while the other is closed.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[GlennUK]

Frederico's suggestions are the best solutions, but if you really can't have the sheet in another book, and must delete the sheet instead of clearing it, then use the INDIRECT function for the links, like this ...

=INDIRECT("SheetA!D"&ROW())

The example I've used creates a linking formula by using a little bit of arithmetic before placing the string inside the INDIRECT function. Of course you could just use a straight string if you want.



Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.