Help - referring to a sheet that does not exist

[gor]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> All,

I'm looking for a simple formula that allows me to refer to a sheet that does not exist or has been deleted.

I have a master workbook to analyse some costs however whenever I delete a sheet (say for project X) to import a new one (from project Y) all the cells return a #REF!. The sheet name and cells are consistent across all imported sheets.

Thanks in advance,

 

[jberwald]

Are you deleting sheet X first and then adding sheet Y? That probably is the problem.

Can you add your new sheet first under a different name; do a FIND and REPLACE to have all your formulas change to that new sheet name; and then you can delete the old sheet and still have your formulas. OR

If instead of your new sheet being in the same workbook - put it in another workbook. Before you open your master sheet - delete your X workbook and replace it with your Y (both x and y having the exact same name and being in the same place). Then when you open your master and update, it should not realize you have switched sheets.

 

[jim may]

As an Alternative, would this idea work for you?

Excel Workbook
	A	B	C
1
2	5	#REF!	HERE, a PREVIOUS REFERENCE GETs DELETED causing the #REF!
3
4	5	500	<<< But the USE of INDIRECT() Can SAVE YOU !! See Formula Here !!!
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,'Oper Data'!$B$1:$B$15,2,FALSE)
B4	=VLOOKUP($A4,INDIRECT("'Oper Data'!$B$1:$C$15"),2,FALSE)