Non-existent Excel Worksheet, but Formulas and Defined names still work?

metalrage

New Member
Joined
Jan 24, 2014
Messages
2
Hi Everyone,
I have an Excel file that is referencing a non-existent worksheet. But the formulas and stuff still work just like nothing is broken (no "#REF" appear). How is this possible?


E.g. Worksheet A! has a VLOOKUP formula referencing a Defined Name in Worksheet B! which is a table range. Except Worksheet B! is nowhere to be found. Yet, the formula still works even as other variables in the formula are updated.


Some additional info:
1) All workbook / worksheet / macros are unprotected
2) There are no hidden worksheets
3) When you open the VBA editor, under MS Excel Objects, the other "ghost" worksheets appear. If you right click on them, you can "View Code". But "View object" option is blanked out. But there are no VBA code for these "ghost" worksheets
4) When you open name manager, the defined names still show Worksheet B! as if it was still there and nothing was wrong.


Anyone knows how this happened?
 
Last edited:

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sounds like some sheets are hidden or very hidden. Either that or the formulas and names are pointing to an external workbook.

Try running this macro in the workbook.

Code:
Sub UnhideAllSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible <> xlSheetVisible Then ws.Visible = xlSheetVisible
    Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top