Dynamically Generate a Numeric Outline in MS Excel
GetUXIndex() is a user defined function for Excel— written in VBScript—that automatically creates and maintains a numeric outline based on cell indentation.
--
Intro
Whenever I create a content inventory or a work-breakdown structure using Excel, I always include a numeric outline (e.g., 1.2.3.0) in the first column. The outline number provides a flexible and extensible reference model that helps keep track of individual pieces of content and individual tasks.
Unfortunately, numeric outlines in MS Excel are tedious to build, difficult to maintain and prone to errors. That’s why I created a user-defined function in Microsoft Excel entitled GetUXIndex() that takes the headache out of numeric outlines.
Note: this function supports multi-level outlines.
How to Set Up
GetUXIndex() does not come packaged with Excel. To add this user defined function to your Personal Excel Macro Workbook, simply copy and paste the following lines of code into a new worksheet in your personal.xlsb file. (If you don’t know how to do that, please check out Microsoft’s instructions on how to create a Personal Macro Workbook.)
Function getUXindex(Selection, Levels)
‘Selection = The current cell ID
‘Levels = How many levels is the numeric outline? For example, 4 = #.#.#.#
Dim container ‘This is the variable that holds the Index # while we build it
Dim IndentCurrent ‘This is the indent level of the current cell
Dim IndentAbove ‘This is the indent level of the cell above the current cell
Dim IndexArray() As String ‘This contains the Index # of the cell above this cell
IndentCurrent = selection.IndentLevel
IndentAbove = selection.Offset(-1, 0).IndentLevel
IndexArray() = Split(selection.Offset(-1, -1), “.”) ‘Grab the index # above the current cell and split it up into an array
IndexContainer = “”
If IndentCurrent = 0 Then ‘If this is the top of the inventory, output “0.0.0.0”
For d = 1 To Levels
If d < Levels Then
container = container & “0.”
Else
container = container & “0”
End If
Next d
Else ‘If not, compare the Indent Level of the current cell to the Indent Level
‘of cell above it and increment the variables in the IndexArray as needed
For j = 0 To selection.IndentLevel — 2
container = container & IndexArray(j) & “.”
Next j
container = container & IndexArray(j) + 1
For b = selection.IndentLevel To Levels — 1
container = container & “.0”
Next b
End If
getUXindex = container ‘Output the fully rendered Index # for this row
End Function
How to Use
Once you’ve added the code above to your Personal Macro Workbook and the Title column is properly indented, you can use the GetUXIndex() function just like any other Excel function such as AVG() or SUM().
GetUXIndex() takes two parameters:
- Cell ID — the cell reference for the Page Title cell (for example, B6)
- Levels — the depth of the outline (for example, 4)
Step One: Starting in column A, enter =personal.xlsb!getUXIndex(B6,4) where B6 is the cell reference of the topmost cell (or root node) of your outline, and 4 is the number of levels to the outline itself.
Step Two: Drag the formula of cell A6 down to the last row of your spreadsheet.
Step Three: When you finish dragging the formula, the GetUXIndex() function will automatically create a unique ID # for each subsequent row.
And that’s it.
You can create a perfectly formatted, fully detailed numeric outline for your content inventory, work breakdown structure or Gannt chart in seconds. And because this is a computed value, the GetUXIndex() will update itself whenever you add, delete, or move rows. (You may need to click CTRL+SHIFT+ALT+F9 to refresh the formulas.)
Troubleshooting
If you experience problems with GetUXIndex(), check the following common problems first:
- Before you share the indexed spreadsheet with your client or team, be sure to copy and paste values column A onto itself. This converts the formulas to plain text. Otherwise, anybody without GetUXIndex() loaded into their Personal Workbook will receive error messages in the A column.
- If you place the first instance of GetUXIndex() in row 1, the function will return errors. Make sure that you start using GetUXIndex in row 2 or after. (Someday, I’ll write a fix for that.)
- If you get security-related errors, you may need to adjust your macro security settings. Please speak to your Cybersecurity team first.
- If your organization prohibits the use of macros or macro-enabled workbooks, you may not be able to use GetUXIndex().
If you need additional help, feel free to write to me at don@donbruns.net.
