How to Reference cells and sheets indirectly in Excel 2016

You have one Excel workbook. You have several sheets in the workbook. You have one summary table on Sheet1 where you want to compile all results. The summary table references to three cells on each sheet. You fill in the formulas, cross-referencing the ranges, Sheet2!A2, Sheet2!B2, Sheet2!C4. This is all fine, it takes a while to do, but you will get there.

Now suppose you add 3 more sheets. Copy the formulas, and then change the sheet references to update your table. Quite cumbersome, but there is a solution to it, reference one cell instead of all with Excels ADDRESS and INDIRECT functions! 

First, we need to look into the ADDRESS function. From Microsofts support page: "You can use the ADDRESS function to get the address of a cell in a worksheet, given specified row and column numbers."

Okeydoki lets begin. If you insert the ADDRESS formula from the Insert Function button, you will see five alternatives. Row_num is the number of the row. Column_num the column number, Abs_num is the reference type, leave it as 1. A1 is the referencing style, we want A1 style so set it to 1 or TRUE. The sheet_text means which sheet we are referring to. We are referring to Sheet2 cell A2.

Your formula should now look like as follows: =ADDRESS(2;1;1;1;"Sheet2") . The result from the formula: Sheet2!$A$2. Note that I am using semicolons (;), not commas (,) as a list separator in the formulas.

What did we do now? We created a string that tells which cell it refers to. We will see why this is beneficial in a moment.

To advice Excel how to use our result, we need the INDIRECT function. Again from Microsofts support: "Returns the reference specified by a text string. References are immediately evaluated to display their contents." Now take the ADDRESS function and wrap it up with the INDIRECT function like this: =INDIRECT(ADDRESS(2;1;1;1;"Sheet2")). The result is: "Hello from Sheet2, cell A2". Replace "Sheet2" in the ADDRESS function to C4 → =INDIRECT(ADDRESS(2;1;1;1;C4)). We also want to write column letters instead of numbers. Replace the second 1 (Column_num) in the ADDRESS formula to: COLUMN(INDIRECT(C7&"1")). You can also replace the row reference to C2.

We are done. Your final formula should look like this: =INDIRECT(ADDRESS(C2;COLUMN(INDIRECT(C7&"1"));1;1;C4)). Your finished spreadsheet should look as below when you have updated everything.