Excel help please - locating a LOOKUP table

uberalles

Good morning

I've been asked to look at an Excel spreadsheet that involves LOOKUP tables.

I am familiar with how they work , then also with the IF statements looking at the data within the table, but have concluded that the problem lies within the content of the table itself.

That is where I'm struggling, I cannot locate the table.

It was sent to me from an external source, the spreadsheet runs macroes that I have enabled, the fact the calculations still run suggest to me that the LOOKUP table is within the spreadsheet I'm working on and not in a spreadsheet elsewhere.

This is an example of one of the calculations

---

>> =IF($A75="","",IF(COUNTIF(Protected_Monthly!$A$3:$A$302,$A75)=0,"Unknown Employee!",VLOOKUP($A75,Protected_Monthly!$A$3:$I$302,4,0)))

So it is the Protected_Monthly table I'm looking for . How do I find it to change it?

I've looked at hidden worksheets within the file, I've looked at protected worksheets within the file, I've tried F5 (goto) but I still can't find it. Any ideas are welcome please?

I seem to recall there is a Function in Excel that takes you step by step through each element of a calculation. Anyone know how to do this, it may help me identify where the table is.

Thanks for any help.

bubblesbonbon

Hi

Try the Name Manager since your protected_monthly table is named

Quote from Excel 2010 help when searching for Name Manager

Manage names by using the Name Manager dialog box

Use the Name Manager dialog box to work with all of the defined names and table names in the workbook. For example, you may want to find names with errors, confirm the value and reference of a name, view or edit descriptive comments, or determine the scope. You can also sort and filter the list of names, and easily add, change, or delete names from one location.
To open the Name Manager dialog box, on the Formulas tab, in the Defined Names group, click Name Manager.

uberalles

Good morning bubblesbonbon

Thank you. I'll give that a try.

Need to pop out now but will let you know how I get on, cheers

bubblesbonbon wrote: »

Hi

Try the Name Manager since your protected_monthly table is named

Quote from Excel 2010 help when searching for Name Manager

Manage names by using the Name Manager dialog box

Use the Name Manager dialog box to work with all of the defined names and table names in the workbook. For example, you may want to find names with errors, confirm the value and reference of a name, view or edit descriptive comments, or determine the scope. You can also sort and filter the list of names, and easily add, change, or delete names from one location.
To open the Name Manager dialog box, on the Formulas tab, in the Defined Names group, click Name Manager.

bingo_bango

I built something similar recently, but as I don't want users playing with lists etc, I have put these on a hidden sheet. Any chance your administrator has done the same?

[STRIKE]
To get it you will need to have the main password to unprotect the sheet, and then use the 'Format' menu bar drop down and choose 'Sheet' then 'Unhide'. This should bring up a list of the hidden sheets. Just choose which one you want to open and I'd lay money that your list is in there somewhere.
[/STRIKE]

Scratch that. I see that you have already looked at the hidden sheets.

Richie(UK)

Hi,

"Protected_Monthly!" suggests to me a worksheet rather than a named range (and, as you suspect, one within the workbook rather than in an external source). When you looked for hidden sheets did you simply look at Format/Sheet/Unhide ? If so, you won't have seen any VeryHidden sheets (something that can be done with VBA/Macros).

Are you familiar with running macros? If you are comfortable with them then press Alt+F11 to open the VBE. Then insert a new module for the workbook concerned and paste in the following:

Sub CreateHyperTOC()
'create a worksheet Table of Contents

    Dim wsTOC As Worksheet, ws As Worksheet
    Dim lRow As Long, bNew As Boolean
    
    Application.ScreenUpdating = False
        
    Set wsTOC = Worksheets.Add(Before:=Worksheets(1))
    wsTOC.Name = "TOC"
    'add a new sheet to the active workbook
    
    With wsTOC
        .Range("A1").Value = "Table of Contents"
        .Range("A3").Value = "Worksheet Name"
        .Range("B3").Value = "Hyperlink to Sheet"
        With .Range("A1").Font
            .Bold = True
            .Size = 12
            .ColorIndex = 3
        End With
        .Range("A3:B3").Font.Bold = True
        'add titles
        lRow = 5
        For Each ws In Worksheets
            If ws.Name <> .Name Then
                .Hyperlinks.Add .Cells(lRow, 2), "", _
                    SubAddress:="'" & ws.Name & "'!A1"
                .Cells(lRow, 1).Value = ws.Name
                lRow = lRow + 1
            End If
        Next
        'add each worksheet to the TOC sheet
        .Columns("A:B").AutoFit
    End With

    Application.ScreenUpdating = True
    
End Sub

This will list all worksheets, including any that are VeryHidden.

What a numpty I am! If you open the VBE and can access the project of the workbook concerned then you will be able to see all worksheets in the Project window (normally top left of the screen) - expand the Microsoft Excel Objects folder for the workbook (ie no need to run the above).

uberalles

bubblesbonbon wrote: »

Hi

Try the Name Manager since your protected_monthly table is named

Quote from Excel 2010 help when searching for Name Manager

Manage names by using the Name Manager dialog box

Use the Name Manager dialog box to work with all of the defined names and table names in the workbook. For example, you may want to find names with errors, confirm the value and reference of a name, view or edit descriptive comments, or determine the scope. You can also sort and filter the list of names, and easily add, change, or delete names from one location.
To open the Name Manager dialog box, on the Formulas tab, in the Defined Names group, click Name Manager.

Hello bubblesbonbon.
I tried this but it didn't show the worksheet, but I appreciate your assistance, thank you.

bingo_bango wrote: »

I built something similar recently, but as I don't want users playing with lists etc, I have put these on a hidden sheet. Any chance your administrator has done the same?

[STRIKE]
To get it you will need to have the main password to unprotect the sheet, and then use the 'Format' menu bar drop down and choose 'Sheet' then 'Unhide'. This should bring up a list of the hidden sheets. Just choose which one you want to open and I'd lay money that your list is in there somewhere.
[/STRIKE]

Scratch that. I see that you have already looked at the hidden sheets.

Hello bingo bango
I appreciate you taking the time to read and responding, thank you.

Richie(UK) wrote: »

Hi,

"Protected_Monthly!" suggests to me a worksheet rather than a named range (and, as you suspect, one within the workbook rather than in an external source). When you looked for hidden sheets did you simply look at Format/Sheet/Unhide ? If so, you won't have seen any VeryHidden sheets (something that can be done with VBA/Macros).

Are you familiar with running macros? If you are comfortable with them then press Alt+F11 to open the VBE. Then insert a new module for the workbook concerned and paste in the following:

Sub CreateHyperTOC()
'create a worksheet Table of Contents

    Dim wsTOC As Worksheet, ws As Worksheet
    Dim lRow As Long, bNew As Boolean
    
    Application.ScreenUpdating = False
        
    Set wsTOC = Worksheets.Add(Before:=Worksheets(1))
    wsTOC.Name = "TOC"
    'add a new sheet to the active workbook
    
    With wsTOC
        .Range("A1").Value = "Table of Contents"
        .Range("A3").Value = "Worksheet Name"
        .Range("B3").Value = "Hyperlink to Sheet"
        With .Range("A1").Font
            .Bold = True
            .Size = 12
            .ColorIndex = 3
        End With
        .Range("A3:B3").Font.Bold = True
        'add titles
        lRow = 5
        For Each ws In Worksheets
            If ws.Name <> .Name Then
                .Hyperlinks.Add .Cells(lRow, 2), "", _
                    SubAddress:="'" & ws.Name & "'!A1"
                .Cells(lRow, 1).Value = ws.Name
                lRow = lRow + 1
            End If
        Next
        'add each worksheet to the TOC sheet
        .Columns("A:B").AutoFit
    End With

    Application.ScreenUpdating = True
    
End Sub

This will list all worksheets, including any that are VeryHidden.

What a numpty I am! If you open the VBE and can access the project of the workbook concerned then you will be able to see all worksheets in the Project window (normally top left of the screen) - expand the Microsoft Excel Objects folder for the workbook (ie no need to run the above).

Hello Richie(UK).
Thank you very much indeed. it was VeryHidden .

I changed this so it now shows and I have managed to fix the error, brilliant, thank you.

I've worked with Macroes and viewed the VBE in the past but never knew about the Alt+F11 control coupled with the ability to make changes. I could have done with knowing that in the past and certainly will use it again.

I really appreciate your assistance, thank you very much :T.