Dynamically Change Sheet Reference in Formulas

[masouder]

Is there a way to create formulas that reference cells on other sheets that allow the user to change the sheet that is being referenced. For example, consider the formula below which adds values from a sheet titled WEST:

=SUM('WEST'!A1:A5)

I'd like to modify the formula so that the reference to sheet WEST can be changed from a user input. For example, assume identical sheets titled WEST, EAST, NORTH and SOUTH. Assume further that these sheet names are provided in a drop-down box. Is there a way to modify the above formula so that if the user selects EAST from the drop-down then the above formula totals values from the EAST sheet instead of WEST?

 

[dreid1011]

You could do that with this, however this simple version has the target cell on each other sheet hardcoded and dropdown list in C1.

=INDIRECT(C1&"!A1")

 

[masouder]

That worked. Thank you.

 

[dreid1011]

You're welcome.