How to use a dynamic sheet name in an Excel formula
Sometimes when writing a complex formula you may not know which sheet of data you want the value to come from. Or, maybe the sheet will change based on some conditions. This is easy to accomplish.
For example, the following formula will return the value in cell A1 of whichever sheet is typed into cell B1.
=INDIRECT(ADDRESS(1,1,,,B1))
The INDIRECT function can be used for many useful dynamically determined values. In this case, the Address function will return the full address of the cell you are looking for based on the current workbook. So, if ”Sheet2″ is typed into cell B1, ADDRESS(1,1,,,B1) = Sheet2!$A$1. If the value in cell B1 were changed to “JanuarySales” the value returned by the Address function would be JanuarySales!$A$1.
The power is in the Indirect function. Indirect will return the value of the cell that you are passing into the function. So, Indirect(”Sheet2!$A$1″) will return the value that is in cell A1 on Sheet2.
The wonderful power of Excel is that any of the parameters can be specified with other functions. Maybe you need to use an If statement to properly determine which sheet to pull from. =INDIRECT(ADDRESS(1,1,,,If(Month(Now())=1,”JanuarySales”,”FebruarySales”)))