If Statements? Look-ups? What's the best approach?

[CLG31415]

I have multiple sheets in my spreadsheet, all named for each of the U.S. states. I'm trying to write a formula that will sum values on the corresponding state page once the user types in a state in cell A2. For example, I have states Alabama through Wyoming, I want to look through every state's sheet until I find Ohio (entered in cell A2) and I want to sum what's in Column L for Ohio. However, the state will not always be 'Ohio', so I do not want to hard-code the word 'Ohio' into my formula.

Is there an easy way to do this?

 

[AliGW]

Yes. Read up on the INDIRECT function, which will help you to achieve what you want.

 

[Tetra201]

@CLG31415:

You would be better off setting up a drop-down list in cell A2 with all the states -- to make your document more user-friendly and to avoid typos. This can be done through Data Validation.

Anyways, here is the formula for the desired sum:

=SUM(INDIRECT("'"&$A$2&"'!L:L"))