Using the text value of a cell in a formula

[menziesthefish]

Hi All,

Is it possible to use the value of a cell in a formula?

Basic example: I have an excel workbook with three separate tabs entitled "Apples", "Bananas", "Carrots". On a separate Summary page I have a drop down list with the options "Apples", "Bananas" and "Carrots". I now want to create a formula that looks up a specific cell (let's say sell "A1") in whichever tab I select from the dropdown list.

i.e. If I choose worksheet "Bananas" from my dropdown, I would like the formula to look up cell "A1" within the tab called "Bananas".

Can a formula be this dynamic?

Huge thanks for any help

Kind Regards

M

[lokicl]

Hi M,

Use the indirect formula to achieve this.

=INDIRECT(A1&"!a1")

Here A1 is the reference cell of you drop down to choose the sheet.

Regards,
loki

If you have issues with Code I've provided, I appreciate your feedback.

In the event Code provided resolves your issue, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

[Ace_XL]

yes..have a look at the INDIRECT function

http://www.cpearson.com/excel/indirect.htm

[menziesthefish]

Thank you for the replies, really appreciated. I've done some research on the INDIRECT function and found a formula that works perfectly for me!

Thanks again

M

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.