How to build a dependency tree when there is no obvious connection between the business model and an Excel model.
A long time ago I spend a lot of time playing the strategy game Civilization. For those not familiar with the game, one of its features was “advances.” Each advance brought with it certain additional capabilities and benefits. Of course, there was a requirement before one could acquire an advance, particularly a set of pre-requisites. For example, pre-requisites for the “Navigation” advance were the “Seafaring” and the “Astronomy” advances. In turn, the Seafaring advance required one to already know “Pottery” and “Map Making”.
I created an Excel worksheet that let me use Excel’s Precedent arrows to understand the optimal path to specific advances as in Figure 1. The advances are shown in red and the benefit(s) of each advance are in black, blue and green. The Excel blue arrows show the pre-requisites for the Seafaring advance.
Figure 1
While the dated worksheet may be of limited value even to Civilization enthusiasts, the technique for creating the dependency tree is unique enough to be of value to Excel consumers. What makes it of value is that the game dependencies (in the context of work this would be the dependencies in the business model) had nothing to do with what Excel considers as dependencies! Consequently, to use Excel’s Trace Precedents feature I had to somehow map the model dependency into Excel formula dependencies.
Figure 2 shows a snapshot of part of the worksheet with a more complex dependency tree than that shown in Figure 1. The dependency lines trace the prerequisites for the Navigation advance.
Figure 2
For those not familiar with Excel’s precedent arrows (and the complementary dependent arrows), the Excel precedent tree visually connects cells that have a formula dependency between them. When a cell contains a formula referencing other cells, Excel can display arrows from the referenced cells to the cell containing the formula. For example, if, in some worksheet, cell B2 has the formula =A1+A2, selecting B2 and clicking Formulas tab | Formula Auditing group | Trace Precedents button will show blue precedent arrows from A1 and A2 to B2.
So, the question is how does one create a precedent tree for the Civilization worksheet? After all, content of a particular cell (the name of an advance) has nothing to do with the content of any other cell! But, to use Excel’s formula auditing capabilities, there has to be a formula connecting the cells!
The trick, if we want to call it that, is to create dummy dependencies using a function that will always return a predetermined TRUE value. First, for the sake of simplicity and understandability, name every cell that contains the name of an advance (cells with a red font). A convenient name would be the name of the advance itself. Then, if an advance (Map Making, for example) has a dependency on another advance (Alphabet, for example), the formula in the cell for Map Making would be =IF(NOT(ISBLANK(Alphabet)),"Map Making",""). In the case of more than one prerequisite, include an AND condition. For example, the Mathematics cell, with a pre-requisite of both Masonry and Alphabet, contains the formula =IF(AND(NOT(ISBLANK(Alphabet)),NOT(ISBLANK(Masonry))),"Mathematics",""). Since every cell has a formula in it, or the name of the advance if there is no pre-requisite, the ISBLANK() function will always return FALSE and NOT(…) will return TRUE. So, the result of the IF function will always be the name of the advance!
To simplify the task of named each cell containing a red font, I used the code in Code Sample 1. Keep in mind that the code, last touched in 1998, is old…it predates VBA 6! The NameAllCells, and its complementary DeleteAllNames do the necessary work. RemoveOneChar and MakeAName are support functions.
Option Explicit
Function RemoveOneChar(ByVal aName As String, _
ByVal aChar As String)
Dim i As Integer
i = InStr(aName, aChar)
Do While i > 0
aName = Left$(aName, i - 1) _
& Right(aName, Len(aName) - i)
i = InStr(aName, " ")
Loop
RemoveOneChar = aName
End Function 'RemoveOneChar
Function MakeAName(ByVal aName As String) As String
aName = RemoveOneChar(aName, " ")
aName = RemoveOneChar(aName, "'")
aName = RemoveOneChar(aName, ".")
MakeAName = aName
End Function 'MakeAName
Sub NameAllCells()
Dim CompleteRange As Range, aCell As Range, _
i As Integer, aName As String, _
AddrAsString As String
Set CompleteRange = Range(Cells(1, 1), _
Cells(1, 1).SpecialCells(xlCellTypeLastCell))
For Each aCell In CompleteRange
If aCell = "" Then
'what I really need is to know if the cell contains anything
'Application.WorksheetFunction.IsNonText
ElseIf aCell.Font.FontStyle = "Italic" Then
ElseIf aCell.Font.ColorIndex <> 3 Then
Else
aName = MakeAName(aCell.Text)
AddrAsString = "=" & aCell.Parent.Name _
& "!" & aCell.Address(ReferenceStyle:=xlR1C1)
ActiveWorkbook.Names.Add _
Name:=aName, _
RefersToR1C1:=AddrAsString
'"=Sheet1!R6C5"
End If
Next aCell
End Sub 'NameAllCells
Sub DeleteAllNames()
Dim aName As Name
For Each aName In ActiveWorkbook.Names
aName.Delete
Next aName
End Sub 'DeleteAllNames
