PDA

View Full Version : [SOLVED] How to Reference Sheet Name in Formula



wadirks
11-24-2009, 12:44 PM
I want to reference a sheet name in a formula. For example, I have three sheets; they are named Summary, 2009-10-31, and 2009-09-30. In the Summary sheet I have the formula ='2009-10-31'!I2+'2009-09-30'!I2. Also, in the Summary sheet I have cells (A1 and B1) with the values 2009-10-31 and 2009-09-30. I would like to reference the two sheets 2009-10-31 and 2009-09-30 indirectly in the above formula using A1 and B1. The values in A1 and B1 will change. The value should correspond to an existing sheet but if it does not can the solution be robust enough to display an #ERROR.

I have not figured out how to do any of this.

RolfJ
11-24-2009, 01:50 PM
This formula should do the trick for you:



=IF(ISERROR(INDIRECT("'" &A1 & "'!I2") +INDIRECT("'" &A2 & "'!I2")),"#ERROR",INDIRECT("'" &A1 & "'!I2") +INDIRECT("'" &A2 & "'!I2"))

wadirks
11-24-2009, 02:27 PM
INDIRECT is what I was looking for, thank you.