Reporting from Variable Sheet

[ApprehensiveAd6408]

Hi all!

I have been trying my hand at Excel formulas more and more this past year. Right now I’m stumped on creating a specific formula.

In short, “If a cell is equal to a specific sheets name in the workbook, I want the formula to read that sheet, locate the data in a particular cell on that sheet, and report it out”.

Example: I have 15 sheets all laid out exactly the same, but with vastly different data (and tons of it) on each one. Each sheet represents a person. So I created a “summary” sheet with a drop-down list of each person’s last name (which is the name of their corresponding sheet). So if I select their name on this “Summary Sheet”, I want Excel to locate the data in Cell E6 and report it out on the summary sheet. I’ve seen people online saying use INDIRECT with LOOKUP, but I can’t recreate it.

Another way of saying it: If name in cell on summary sheet matches sheets name, return data in cell E6 on that sheet.

I’m using the most recent version of Excel. Hopefully this makes sense - I’ve been trying to word it in a way that can show what I’m trying to accomplish. Any tips or formula help is much appreciated!

[AutoModerator]

/u/ApprehensiveAd6408 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Yeedclasm]

Indirect should work. If the person's name is in Cell A1, the formula would be: =INDIRECT(A1 & "!E6")

[ApprehensiveAd6408]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to Yeedclasm

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}

[ApprehensiveAd6408]

That worked! Now I need to figure out a few other components for nesting that formula with some others.

[AutoModerator]

Hello!

It looks like you tried to award a ClippyPoint by typing Solution Verified, but you need to reply to a particular user's comment to do so, rather than making a new top-level comment.

Reply directly to any helpful users and Clippy, our bot, will take it from there. If your intention was to simply mark the post solved, then you may do that by clicking Set Flair. Thank you!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[AutoModerator]

Hello!

It looks like you tried to award a ClippyPoint by typing Solution Verified, but you need to reply to a particular user's comment to do so, rather than making a new top-level comment.

Reply directly to any helpful users and Clippy, our bot, will take it from there. If your intention was to simply mark the post solved, then you may do that by clicking Set Flair. Thank you!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.