Quick Summary for multi-tab workbooks

Hi all,

You inherited a workbook with multiple tabs, with all tabs containing the same structure. Maybe the tabs are split by region, or by month, or anything else. Of course, for proper analysis, you’ll want to consolidate all the tabs in one data tab, but this is not our goal right here.

Question: Assuming you need to keep the file in the current format, how can you quickly look at data from a specific cell in all the tabs at once.

Answer: Using the INDIRECT() function, which turns a string reference into a range you can use in formula.

In a new sheet, build a table with your tab names in a column (you can also use my Table Of Contents macro), and the cell references in a row. Concatenate column and row to get a properly formed range reference, e.g. FL!A5, or ‘Northern Division’!B6:C20. Use the indirect function to return the reference in whatever formula you need. If you reference one cell, you can retrieve it directly. If you reference a range, you need to use it in a formula that accept ranges (SUM, COUNT, COUNTIF, etc.). If your tab name has special characters, such as a space, make sure you surround it with inverted commas.

In the screenshot below, the formulas are ready to be copied down, to pull cell B50 for each of your tabs, and the sum of range D2:F25 for each tab.

INDIRECT Function

INDIRECT Function example

 

Try it, you might like it.

 

Thomas

 

For more reading on the INDIRECT function, you can see the Microsoft Office website here.

Advertisements

Table Of Contents for multi-tab workbooks

Hi all,

 

A quick and dirty way to create a table of contents for a multi-tab excel workbook. Run the attached macro and it will create a new tab, with the list of all existing tabs and a hyperlink to each of them.

Try it, you might like it.

Sub IndexAllTabs()
‘creates table of contents for workbook
Dim sht As Worksheet, i As Long
Dim shtDone As Worksheet

Set shtDone = ActiveWorkbook.Sheets.Add

On Error Resume Next
shtDone.Name = “TOC”

If Err.Number <> 0 Then
    ActiveWorkbook.Sheets(“TOC”).Delete
    shtDone.Name = “TOC”
    Err.Clear
End If
i = 1

For Each sht In ActiveWorkbook.Sheets
    If sht.Name <> shtDone.Name Then
        shtDone.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 1), Address:=””, SubAddress:= _
            “‘” & sht.Name & “‘!A1”, TextToDisplay:=sht.Name
        i = i + 1
    End If
Next

End Sub

 

Thomas