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

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.

Automatic increment

By now, you probably know how to automatically increment series, enter the first two cells of the series, select them, place your pointer on the bottom right corner (until it turns into cross) and drag with a left click.

Now, for dates, you can do the same and more by just typing the first date (remember Ctrl+; will enter today’s date) and dragging with the right click. When you release, you can pick your increment between days, weekdays, month and year. You can also pick Series if you need more flexibility.

right click drag

Try it, you might like it.

Thomas

Hyperlink from Word / Excel to specific cell in excel file.

Happy Halloween,

It’s Internal Control Procedure time again and you have all your procedures in Word, trying to hyperlink to excel files. All is well, you Insert \ Hyperlink (or right-click \ Hyperlink), select the destination file and you’re done. BUT, it doesn’t really open where you want it. To make sure it opens at the right cell, add the following to your file path: #’SheetName’!CellAddress, as in #Sheet1!A.

You can of course, update the text to display so it doesn’t show the whole path. Check out screenshot below.

Address bar and Text to Display bars in Edit Hyperlink windo

Thanks Benjamin for this suggestion,

Try it, you might like it.

Thomas