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

2 Answers 2

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

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.