Excel - Create cell reference without use of INDIRECT()

Question

I have a larger Excel-file, with a summary page that draws data from multiple worksheets (no macros allowed in this scenario). In the summary, I need simple references to cells from other worksheets (e.g. ='foo'!A1). However, as directly linking the cells was found to be a rather tedious task due to the amount of worksheet I made use of helper columns for the references to the worksheet, row and column of the cell to be linked. Currently, I'm using the INDIRECT function for this case, which works just as expected in the following form:

=INDIRECT(ADDRESS("Rows helper", "Columns helper", 1,1,"Worksheet helper"))

Due to its volatile nature, the INDIRECT function slows down the worksheet significantly though and makes the file hardly usable. My question is now, whether there is a non-volatile alternative for such a use case which does not make use of macros.

Thanks a lot for your suggestions!

Here is an example of the Excel sheet described above.

P.S.: I could use macros for the developing phase, however I would need to remove them once the greater part of the development is over.

Answer 1

Since you already seem to have the cells you want to reference mapped out, we can use that to produce direct cell reference formulas. To do so requires three steps:

1. Change your formulas to: ="="&ADDRESS("Rows helper", "Columns helper", 1,1,"Worksheet helper")
2. Select the entire range of formulas, copy, then Paste Special Values in place. This will give you a range of cells that show formulas, but don't actually do anything.
3. With the range still selected, do a find/replace for the equals sign (=). You want to replace all equals signs with equals signs. This forces the text to be read as a formula instead of text.

You can then remove the helper entries if you'd like.