Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

When you create a table in Excel (I'm using Excel 2007) you can give it a name that shows up in the Name Manager dialog. However, the name has workbook scope. I'd like to have a table with a name scoped only to the worksheet if possible (just like named formulas/ranges can be). Is it possible?

For example, I'd like to have a table named 'Notes' on several different worksheets. Any given table would only be referenced from the worksheet it is actually on, and I'd rather not have to have each worksheet have a table with a different name. I'd also rather be able to see the table name grouped with other named formulas scoped to that worksheet when I sort by scope in the Name Manager dialog.

share|improve this question
    
Have you tried testing in VBA using the same syntax as sheet-specific named ranges? I don't have 2007 here or I'd check it for you. –  Lance Roberts Aug 9 '10 at 17:09
    
@Lance, if I try to name a ListObject from VBA to e.g. "Scratch!MyTable", the call appears to work, but the name gets silently changes to "Scratch_MyTable" and still has workbook scope. If I just try to set it to "MyTable" and there is already a table named that on a different sheet, it ends up "MyTable_1". Of course, trying those things from the table design menu results in "can't do this" type messages. –  jtolle Aug 9 '10 at 22:45
    
It's interesting that the ListObjects collection is local to the Worksheet object, though. It seems like Excel would be fine with two tables in two different collections having the same name, but I'm starting to think the answer to my question is "no"... –  jtolle Aug 9 '10 at 22:47

3 Answers 3

up vote 2 down vote accepted

For completeness, I'm pretty sure the answer to my question is "no".

Of course I'll un-accept this and accept an answer that shows that this is wrong, or one that explains why it's right.

share|improve this answer

This may be helpful as a workaround:

    'Given a worksheet name and a table(ListObject)name "prefix," return the actual name of the ListObject on the sheet.
    'For example, if a worksheet containing a table called "ConfigTbl" is duplicated, it will get a name
    'like "ConfigTbl3279" on the worksheet copy.
    'This is a workaround for the fact that Tables are worksheet-level objects;
    'Excel doesn't have local/worksheet-level tables.
    'If you pass in one of the "Special Item" qualifiers, then the function will return the specified
    'range as an object (e.g., "[#All]" for the whole table or "[#Headers]" for the column names).
    'Returns #N/A if error.       


     Public Function tableName(wksht As String, tblName As String, Optional specialItem)

     Dim lObj As ListObject

     On Error GoTo ErrHandler
     tblName = UCase(tblName)

     For Each lObj In Worksheets(wksht).ListObjects
         If (UCase(Left(lObj.Name, Len(tblName))) = tblName) Then
             tableName = lObj.Name
             If Not (IsMissing(specialItem)) Then Set tableName = Evaluate(tableName & specialItem)
             Exit Function
         End If
     Next lObj

    ErrHandler:
        tableName = CVErr(xlErrNA)

    End Function
share|improve this answer

I've been running into the same problem. In my VBA code, I resorted to looking at the objects for the active worksheet and checking against the first 6 letters (when I copy a worksheet with tables, Excel 2007 simply appends a number to the end of the table names).

' First get table names on this worksheet (copying worksheet changes names of tables)
Dim oSh As Worksheet
Dim oLo As ListObject
Set oSh = ActiveSheet
For Each oLo In oSh.ListObjects
    Application.Goto oLo.Range
    'MsgBox ("Table found: " & oLo.Name & ", " & oLo.Range.Address)
'    pathsTable =
    Select Case Left(oLo.Name, 6)
        Case "tblDec"
            decTable = oLo.Name
        Case "tblPat"
            pathTable = oLo.Name
        Case "tblPro"
            processTable = oLo.Name
    End Select
Next

Then in the rest of my code, I use the new names: decTable, pathTable, and processTable.

A simple work-around is to create names with local scope that refer to the table. So you'll have two names for each table - one with global scope and one with just the current worksheet scope. (In the Define Name dialog box, name the new range "Notes", change scope to the worksheet and the "Refers to" to =Table1 (or whatever Excel named the table)). Do this on the first worksheet, then copy the worksheet. The tables will have the same names with scope for just that worksheet. The drawback is that you can't use normal table referencing when using the duplicate name, e.g. "=average(Names[Column1])" doesn't work. :( If you are using just a small selection of columns of the table, you could create a name for each column that you want to use elsewhere, "myColumn1" refers to "=Table1[Column1]".

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.