[SOLVED:] How to Reference Sheet Name in Formula

How to Reference Sheet Name in Formula

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.

Give this formula a try

This formula should do the trick for you:

Code:

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

INDIRECT is what I was looking for, thank you.