Excel indirect reference produces a ref error

Question

I am trying to dynamically reference a cell on a different open and visible worksheet on the same workbook by using the INDIRECT function. When I make the cell reference with a static value, it works fine, but does not if the reference is not static. With the code snippet below, $A$31 is the sheet name, F9 is the cell reference for that sheet. This works fine. However, it is the F9 than needs to be dynamic, such as G9, H9 and F10, F11.

=INDIRECT("'" & $A$31 & "'!F9")

I would have thought the following snippet should have been the change to make. However this only results in a #REF error

=INDIRECT("'" & $A$31 & "'!" & F9)

What changes do I need to make, and why am I getting the error?

Answer 1

The formula =SUM(B2,C2,D2) is used in column E in the following example.

A #REF! error would occur if column B, C, or D were deleted. We'll eliminate column C (2007 Sales) in this scenario, and the formula will now be =SUM(B2,#REF!,C2). When you use explicit cell references like this (where each cell is referenced separately, separated by a comma) then delete a referenced row or column, Excel is unable to resolve the reference and returns the #REF! error. This is the main reason why explicit cell references are not recommended in functions.

If you mistakenly erased rows or columns, you can restore them by using CTRL+Z or clicking the Undo button on the Quick Access Toolbar.

Make the formula, like =SUM, utilise a range reference instead of individual cells (B2:D2). You can now delete any column inside the sum range and Excel will adjust the calculation automatically. For a sum of rows, you could write =SUM(B2:B5).