Trying to find all the data tables in my workbook

[jamie_in_nj]

Hi, I am using Excel 2010. I am digging through a workbook with 80-some worksheets. There is one worksheet with 11 data tables. When the workbook refreshes, there is a note at the bottom saying something about 21 data tables. I am not sure where the other 10 are. In an online post, someone said that the Name Manager should show them. But in Name Manager, when I filter on Tables, the list goes blank, and all the icons are the same for the other named ranges. Is there another way to find the data tables? Thanks.

 

[Jerry Sullivan]

Hi jamie_in_nj,

I don't know why the Name Manager is going blank when you filter for Tables.

You could try using this code to list the names and adddresses of each Table in your Workbook.

Sub List_Tables_In_Workbook()
    Dim wsSummary As Worksheet
    Dim tbl As ListObject
    Dim n As Long, lRow As Long

    
    '--add summary sheet with headers
    Set wsSummary = Worksheets.Add(Before:=Sheets(1))
    Range("A1:C1") = Array("Table Name", _
        "Found on Sheet", "at Range Address")
    lRow = 1 'Header Row

    
    '--step through each sheet and list each table
    For n = 2 To Worksheets.Count
        For Each tbl In Worksheets(n).ListObjects
            lRow = lRow + 1
            With wsSummary
                .Cells(lRow, "A") = tbl.Name
                .Cells(lRow, "B") = tbl.Parent.Name
                .Cells(lRow, "C") = tbl.Range.Address
            End With
        Next tbl
    Next n

    
End Sub

 

[jamie_in_nj]

Hi thanks for the response. The code does not seem to list my data tables though. I tried it in the one workbook I had. It created a new worksheet. That sheet just has the headers of Table Name, Found on Sheet, and at Range Address, but nothing below them. I created simple workbook and created a Data Table, and tried it again, and got the same result. I can post the simple workbook here, if people do that. Sorry, I am not familiar with the protocol on the site, and if I should post a workbook. Thanks for any help!

 

[Jerry Sullivan]

Hmm..perhaps those aren't ListObjects but some other type of Table-like Object.

This forum doesn't support attachments- when necessary people either post to a sharing site like Box.com or exchange email addresses through a Private Message (PM).

If you post it, or send me a PM, I'll take a look.

 

[jamie_in_nj]

Thanks I sent you a PM. If you are OK with PM-ing me back with your email address, I will send you a simple example workbook.

 

[Jerry Sullivan]

Hi Jamie,

I reviewed your file and can see why the tables don't show up in the Name Manager and my code didn't count the tables.

I had assumed that these were ListObject tables, which can be created by:
Selecting a valid range then from the Ribbon > Insert > Table

These ListObjects were called Lists prior to xl2007, and they are now commonly referred to as "Tables".
ListObjects show up in the Name Manager and you can filter the Names to just show "Tables".

What you have in your file are not "ListObjects" but rather "Data Tables" (Which is exactly what you've correctly called them from the beginning, so the misunderstanding is mine!)

I haven't had experience with Data Tables, so I appreciate that you've helped me become aware of them.

In Excel 2010 Tables can be created from the Ribbon through Data > What-If-Analysis > Data Table...
This link provides some instructions
Calculate multiple results by using a data table - Excel - Office.com

So now that we understand why the Name Manager and ListObject Macro didn't work, we can revisit the problem you are trying to solve.

Here's my uncertain understanding and I welcome corrections from others who have more knowledge on this:

Data Tables are not Objects within the Excel Object Model, so they can't be easily counted or listed the same way ListObjects, PivotTables or Shape Objects can be handled with VBA. Rather they are Array formulas that use the TABLE() function leaving a formula that looks like {=TABLE(,B7)} where B7 is the "What If" Column Input.

One approach to counting/listing the Data Tables could be to use VBA to search all formulas and gather unique instances of "TABLE(*,*)". That seems more complicated than it should be, so I'll wait a bit to see if someone else can share a better approach.

 

[JoeMo]

Hi Jamie,
One approach to counting/listing the Data Tables could be to use VBA to search all formulas and gather unique instances of "TABLE(*,*)". That seems more complicated than it should be, so I'll wait a bit to see if someone else can share a better approach.

I'd be inclined to go this route Jerry as I can't think of any other distinctive feature of a Data Table. These can take a long time to calculate and with 21 of them it must be nearly a lifetime so it's worth finding and eliminating them where possible. When I use them, I always set Formulas>Calculations to "Automatic Except for Data Tables" which avoids long waits when anything triggers a calculation.

 

[jamie_in_nj]

Hi Jerry, thanks for doing the research, and suggestion, and thanks to Joe as well.

Currently the calculation happens fairly quickly, within seconds, so I am OK with the timing. I am able to find the Data Tables by going through and visually looking for them. The functionality is quite helpful, although their setup was not that intuitive to me, but I have gotten past that hurdle.

Jamie

 

[msweetmo]

I was searching for a solution for this and figured it out based on reading the thread so I thought I would leave my findings for future searchers... if you you use the Find feature (Ctrl-F) and search for "Table(" it will find all the cells that are part of a "Data Table" (What-if analysis result). Remember to change the 'Within' field in Advanced to "Workbook" if you want to find across multiple worksheets

 

[Jerry Sullivan]

Hi msweetmo,

Welcome to MrExcel and thank you for sharing your findings.