Daisy chains: Why to avoid linking to links
When you are teriffed to delete anything in a model due to #REF explosions, daisy chains may be the cause.
As we build up our model using calculation blocks, we will have lots of links in the model. In the last chapter, we saw that we could either copy an existing link or link back to the source.
In this chapter, we will talk about why we should never link to a link.
What do we mean by linking to a link?
In the last chapter, we copied the Operating Period Flag from row 37 of our calculation into our Operations and Maintenance cost block.
The original link in row 37 and the copied link in Row 43 both point back to the Timesheet.
If we had linked to the link, row 43 would be pointing to Row 37 rather than to the Timesheet.
Let's say we set our model with a series of links, all linking to each other.
Conceptually it would look like this.
Each link points to another link, which points to another link. And so in through the chain.
All of the calculations in the chain would work fine until we want to delete something from the model.
Let's say we no longer need link B, and we decide to delete it.
The problem is that link B is part of a daisy chain of links. Usually, in a model, we have no visibility that a daisy chain of links exists. When we delete link B, the model instantly blows up with #REF errors everywhere
Many of you will have had this experience. Models where you are terrified to delete anything because the model will blow up. This situation is usually caused by Daisy chains.
The other negative consequence of Daisychains is that the navigation that we have come to rely on using Ctrl+[ no longer works.
We are going to avoid this by avoiding daisy chains.
If we always copy links rather than linking to them, they will all point back to the source calculation. Our navigation keystroke navigation will work well. We will also be able to delete code as we need to.
If we need to delete any of the links, the rest of the model is not affected. In this example, we can delete link B without links C, D, and E being affected.
Productivity Pack inbuilt protection against Daisy chains.
We have seen in Skill 2 that the quickest way to create a link is to use the Ctrl+Shift+q macro. If you try to create a link to a link, the macro will warn you.
Let's say, instead of copying the link in row 37, I try to create a link to it.
I follow my Skill2 process:
First, I copy the label in E37.
I then come to where I want to put the link in row 43 and hit Ctrl+Shift+q.
The macro will warn me that a "possible daisy chain" is detected. The macro has seen that what you copied in row 37 was a link.
The macro asks you if you'd like to create the link from the source rather than the link.
If you say Yes, the macro will create the link but will link to the Operating Period Flag source calculation on the Timesheet. The macro will "look through" the link you are trying to copy and link to the source, thus avoiding the daisy chain.
If you say no, the macro will create the daisy chain link.
Comments
Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.