Getting Value of Named Cell After Using User Input to Construct Cell Name

Apologies if the title is confusing, it does describe what I want to do.

I want the ability to type in text, then have Excel use that input to construct a cell name. Then I want to display the value of the cell that has the constructed cell name.

So, for example, if a cell has the name JOHN_SCORE, I want to able to type in JOHN, then have Excel construct the cell name JOHN_SCORE, then display the value of JOHN_SCORE in a designated cell.

So far everything I have tried results in Excel displaying the text "JOHN_SCORE" *as* the value, rather than using the text as the cell name and getting the corresponding value.

I can use CONCATENATE(input, _SCORE) to construct the cell name, but I can't figure out how to tell Excel that this is a cell name,not a cell value.

The attached sheet is a (very) simplified example of what I want to do.

Any help appreciated!

You can do this:

=INDIRECT(input_cell&"_SCORE")

Hope this helps.

Pete

Excellent! Thank you Pete! That worked like a charm.

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete