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.

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

The Great Splitter And Sender Of Reports

On a monthly basis, at least, you’re faced with slew of reports that need to be sent to various managers, with some managers getting one part of the report, some getting others, and some getting all the parts.

You will usually have to copy tabs to new workbooks, save temporary files and e-mail them.

Here comes the Great Splitter And Sender of Reports to make your life easier and generally better: use the Command Center tab to define who gets which tabs in the workbook, set the parameters of the e-mail, etc. then press the Split and Send button to, you guessed it, split and send the tabs. You just have to go to Outlook to press Send on all those e-mails and you’re done.

To include that functionality to one of your report files, copy the Command Center tab and the module mSplitAndSend to your report workbook. Voilà.

Here’s the file: The Great Splitter and Sender of reports.

Try it, you might like it,

Thomas

Explode two lists with all values of the other

From time to time, and I must admit, it doesn’t happen often, but often enough to justify making a macro, you will want to match two lists, e.g. a list of employees and a list of G/L accounts, and get as a result a line for each possible combination of employees and accounts.

Image

Here’s the excel version.

1. Copy the code listed at the end of this post in a standard VB module

2. Select both ranges: you select the employee list with the mouse, then press Ctrl, release the mouse button (while still holding the Ctrl key down) and select the GL list.

3. Run the macro: you now have a new tab with the expanded list ready for consumption.

Try it, you might like it.

Thomas

Here’s the code:

Sub ExplodeLists()
Dim rg1 As range, rg2 As range, shtDest As Worksheet, rgCell As range
Dim lLoop As Long, lRowDest As Long

‘turn off updates to speed up code execution
With application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

If selection.Areas.Count <> 2 Then
MsgBox “Select two areas to join”
Exit Sub
End If

Set rg1 = selection.Areas(1)
Set rg2 = selection.Areas(2)
Set shtDest = Worksheets.Add

lRowDest = 1

For lLoop = 1 To rg1.Rows.Count
shtDest.Cells(lRowDest, 1).Resize(rg2.Rows.Count, rg1.Columns.Count).Value = rg1.Rows(lLoop).Value
rg2.Copy shtDest.Cells(lRowDest, 1 + rg1.Columns.Count)
lRowDest = lRowDest + rg2.Rows.Count
Next

With application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With

End Sub

Reports of all stripes with conditional formatting

Hi all,

Imagine you want alternating rows (or columns) in a report to have alternating colors. I don’t know, maybe you’re missing the days of tractor-feed paper.

One option is to convert your data in a table (Ctrl+T, or Insert \ Table). Other advantages come with the table like the ability to reference ranges in formulas with names instead of addresses, but the table doesn’t work in all cases (but I definitely should write something about tables soon).

An easy option, if you can’t use tables is to use conditional formatting.:

  • Select your data
  • Home \ Conditional Formatting \ New Rule
  • Use a formula to determine which cells to format
  • Use the following formula =ISODD(ROW())
  • Set your format, validate, you’re all set (you can also use ISEVEN, and swap COLUMN() for ROW() if you want vertical rather than horizontal stripes).

Image

Try it, you might like it.

Thomas

Right-click in drag

You obviously know that you copy / paste a cell by clicking on its lower right corner, and dragging it down or across while holding the left mouse button down.
For some cell values, e.g. dates or numbers, you can even switch between an incremented copy and a straight copy by holding or not the Ctrl key. By default, dates will increment and numbers won’t, and holding Ctrl will switch the behavior.
NOW, if you perform this dragging with the right-click instead of the left-click, you’re getting a lot more options (see screenshot below), like filling in weekdays, or months. This is quite the time saver.

Options stemming from a right-click drag

Try it, you might like it.

Thomas

PS: Try selecting multiple cells before dragging and see how that affects the results you’re getting.

Find the text cells masquerading as numbers.

From time to time, an import gone awry will leave you with values that look like numbers but are actually text. They won’t total, they won’t compute in formulas and won’t be picked up by a pivot table.

If you have a whole column of them, usually a Data \ Text to columns will take care of the issue, but sometimes that’s not your best tool.

A quick way to find out which cells are not really numbers (and a quick way to find lots of other types of cell) is to use the Goto…Special function. Select the range containing the culprits, press F5 and you have the Go to menu, press the Special button and you will get all the Special Cells option. Select the appropriate one, in this case, Constants \ Text and voilà, the text cells are selected. You can now manually address the issues.

Try it, you might like it.

Thomas