VLOOKUP reference

VLOOKUP reference

Didn't find your answer?

I have inherited a spreadsheet which contains this VLOOKUP:

=VLOOKUP(E116,Oncosts!$A$2:$B$173,2,FALSE).

Nice and simple! However I want to change the source 'oncost' list as some of the values have been amended.  What I cannot find is where the previous user has hidden that list.  There is no tab named 'oncost', and there are no hidden tabs.  I have looked in the name manager and there is nothing named 'oncost'.  It doesn't look to be referencing another workbook - this workbook is stored on SharePoint if that makes any difference.

When I add lines to the list and drag the formula down, it still works and brings back correct values, so the reference must still be in some way live and valid but I'm blowed if I can find it!

Thanks in advance for shedding any light...

Sharon

Replies (4)

Please login or register to join the discussion.

avatar
By nogammonsinanundoubledgame
10th Dec 2015 18:19

Might it be 'veryhidden'?

You can make a worksheet 'hidden' or 'veryhidden'.

It requires a VBA instruction to make it veryhidden, the effect of which is that it is excuded from the list of hidden tabs that you then want to make visible by right clicking on the tabs.

 It is a bit [***] to go to that much effort, to be frank, and rarely justified. You can still make it visible again but you have to go through VBA to do it. Something like:

Sub Designmode()
Dim wWS as Worksheet
With Application.ThisWorkbook
.Unprotect
For each wWS In .Worksheets
With wWS
.Visible = xlSheetVisible
.Unprotect
End With 'wWS
Next wWS
End With 'Application.ThisWorkbook
End Sub

With kind regards

Clint Westwood

Thanks (0)
avatar
By paulwakefield1
10th Dec 2015 09:04

I suspect

that a macro has been run using the xlveryhidden property.

 

Try running:

Sub UnhideSheet()
    Sheets("Oncosts").Visible = True
End Sub
 

Edit: Should type quicker.  :-)

Thanks (0)
By Paul D Utherone
10th Dec 2015 09:25

Or, presuming it is not password protected
Press Alt & F11 to open the VBA editorlook for the hidden sheet called OnCostsclick on the sheet; andin Properties (press F4 if the properties window is not showing) change Visible from xlSheetVeryHidden to xlSheetVisible

Thanks (0)
avatar
By cornwallsharon
10th Dec 2015 09:31

It worked!

Thankyou both very much.  It was indeed 'very hidden' and a macro has done the trick.

 

What a strange thing for my predecessor to have done.

 

I would never have got there by myself.

 

Regards,

Sharon

Thanks (0)