Opening the vault on Excel, part 8

Excel expert Neale Blackwood CPA began writing his monthly Excel Yourself column in INTHEBLACK magazine in 2002. He recently “opened the vault” on those early articles to review the information and add newer and easier solutions or built-in functions. This month: Contents sheets, General Purpose Macros and more.

1. I regularly create a list of sheets in a Contents sheet in my files. I then create hyperlinks to the sheets. Can this process be automated?

Having a Contents or an Index sheet is a great idea for larger files. It can speed up navigation within the workbook. The Contents sheet is typically the first or second sheet in the file.

The process takes a lot of effort and is ideal for a macro as it is a repetitive process. You select a blank cell and run the macro below.

I have rewritten the macro slightly and included error handling.

Sub SheetListHyperLink()
'This macro creates a list of hyperlinks to all sheets in the file
'The list starts from the selected cell
Dim ws As Worksheet
Dim r As Integer
On Error GoTo HandleError
r = 0   'counter use to move the row down from the active cell    
    For Each ws In Worksheets        
        If Selection.Offset(r, 0) <> "" Then GoTo HandleExit        
        ActiveSheet.Hyperlinks.Add Anchor:=Selection.Offset(r, 0), Address:="", SubAddress:= _
            "'" & ws.Name & "'!A1", TextToDisplay:=ws.Name            
        r = r + 1            
    Next ws    
HandleExit:
    Set ws = Nothing
    Exit Sub    
HandleError:
    MsgBox "Error encountered"
    GoTo HandleExit
End Sub

I will include this macro in the companion file for this post. If you are new to macros, they are a large topic and worth exploring further. 

Warning

Running a macro clears the Undo list and so macros cannot be undone. Always save the file before running a macro. That way you can always close the file without saving it if the macro has caused an issue.

The above macro starts at the active cell, and it adds an entry below for each sheet in the file. It also creates a hyperlink for that sheet. If a cell underneath contains an entry, the macro stops and the entry is not overwritten. 

This macro started out as a recorded macro, and I modified it to loop through all the sheets in the file and create the list of hyperlinks.

The list isn’t dynamic. If a sheet name changes, the hyperlink will be broken and you will need to re-create the list. Remember you need to remove the entries before running the macro as the macro won’t overwrite any existing entries.

General purpose macros

The above is an example of a general purpose macro. You can run this in any file without having to save the macro in the file. For those types of macros, you can save them in your Personal Macro Workbook. This is a special Excel system workbook file that is used to save recorded macros.

2. When I insert a row between the data and the SUM function, Excel will usually amend the SUM function to include the new row. However, sometimes it doesn’t do it. Is there a way to automatically include the row above the cell with the SUM function?

There is a technique to always include the range up to and including the cell above the SUM function cell. This ensures that all of the values above the SUM are included. 

This solution doesn’t handle the case where a row is inserted above the SUM range. The previous question used the Offset VBA command in a macro. There is an OFFSET function in Excel, and it is part of the solution.

This technique can be used for handle summing across the sheet as well. See the example in Figure 01.

Figure 1.

In Figure 01 the formula in cell B6 is:

=SUM(B2:OFFSET(B6,-1,0))

Using the OFFSET function after a colon means you are creating a flexible range reference. In the case above, the range always starts at cell B2, but the end cell will be determined by the cell the formula is used in. See note below about circular reference errors.

The OFFSET function allows you to specify an anchor cell and then move the reference from that cell by referring to a number of rows and/or columns. A negative row number moves the reference up, and a positive number moves it down. A negative column number moves the reference to the left, and a positive number moves it to the right.

When working in Excel, you always refer to rows first and columns second. This rule applies to all functions that refer to rows and columns.

The above formula specifies the anchor cell as B6. It then moves the reference from B6 up one row (negative one used) and zero columns. Hence it refers to cell B5 as the end of the SUM range.

If we insert a couple of extra rows (as per Figure 02), the formula automatically adapts.  

Figure 2.

The formula in cell B8 is: 

=SUM(B2:OFFSET(B8,-1,0))

The SUM range now refers to cell B7 as the end of the range.

As mentioned earlier, if we insert a row between row 1 and row 2, then the formula will not include that inserted row.

To handle horizontal ranges, the formula is only slightly different. The formula in cell E2 is: 

=SUM(B2:OFFSET(E2,0,-1))

This doesn’t move the row reference, but it moves the column reference one column to the left.

If we insert a couple of columns, the formula handles the change. In Figure 03, the formula in cell G2 is:

=SUM(B2:OFFSET(G2,0,-1))

Figure 3.

Circular reference errors

A circular reference error is caused when a cell refers to itself within a formula. This is usually a mistake, but there are techniques that use circular references to create complex calculations.

In general, circular references are avoided in Excel. The above technique does refer to itself, but because it uses the OFFSET function to move the reference, the cell it refers to is not itself, and so it avoids a circular reference error.

Warning

The OFFSET function is volatile. This means it calculates every time Excel calculates, whether it needs to or not. This can affect the calculation speed of your file. These days, this is less of an issue since computers are so fast that you wouldn’t notice it in most cases.

3. How do I set up titles when printing in Excel? I can see the area I need to change, but it is greyed out in the Sheet tab of the Setup section of Print Preview.

I’m not sure why this is an issue, but it has been a common problem in Excel since the early days. The Page Setup dialog within the Print Preview won’t allow you to create or amend the print titles.

Print titles are the rows you want to repeat at the top of the printed page and the columns you want to repeat at the left of the printed page.

It’s a lot easier to access print titles in the latest versions of Excel. In the Page Layout ribbon tab, print titles are shown as an icon on the right of the Page Setup section – see Figure 04.

Figure 4.

Figure 05 has an example of the “greyed out” print titles.

Figure 5.

If you click the Print Titles icon you will go directly to the Sheet tab of the Page Setup dialog, and you can create or amend the print titles as per Figure 06

Figure 6.

The companion video and Excel file will go into more detail to demonstrate these techniques.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services to organisations around Australia. Questions can be sent to [email protected]


Like what you're reading? Enter your email to receive the fortnightly INTHEBLACK e-newsletter.

Recommended for You

READ MORE

Opening the vault on Excel, part 8


READ MORE

Opening the vault on Excel, Part 7


READ MORE

Opening the vault on Excel, Part 6