When you create a formula or a range that refers to cell A1 and you want it to ALWAYS refer to A1 no matter what – you have at least a couple of options.
Let’s take an example where we add up the range A1:10 – see image below.
Then someone inserts rows above A1 – see image below.
How can we make sure the range adjusts to include the inserted rows?
INDEX function solution
We can use the INDEX function to create a reference to the top left cell in the sheet – see image below.
This formula will always refer to cell A1.
Note: this will refer to cell A1 even if columns are inserted – see image below.
INDIRECT function solution
You can also use the INDIRECT to ensure you always refer to cell A1. See image below.
The INDIRECT converts the text A1 into a reference to cell A1.
Note: This also refers to cell A1 even if columns are inserted – see image below.
Volatile Warning
The INDIRECT function is volatile. This means it calculates every time Excel does. Most functions only re-calculate when their reference change. In larger models, volatile functions can impact calculation speed.
Auditing
Both these techniques impact being able to audit the formulas.