Pull Data from Multiple Sheets into a Summary Sheet

[AlannaBBB]

I have 170 sheets which are all formatted the exact same way. Each sheet represents a store and its purchases. I want to create another sheet which will act as a summary sheet and display certain information from each store sheet. Down Column A of the Summary Sheet I have each store name (each store name is also shown on the store sheet in A1 and each sheet is named as the store). Is there a formula that can act almost as a reverse vlookup to look across the entire workbook, match the store name in column A to the store name in A1 of the corresponding sheet, and then return the value in E5 of the store sheet to Column B in my summary sheet?

[hoyasaxa215]

Attached is a template using the "=indirect" function. This template is a bit more complicated than necessary for your purposes, but should illustrate an easy way to populate your summary worksheet. If it's unclear, please respond (ideally with a sample of the file you're using so we can make the necessary changes).

[AlannaBBB]

This works great when I remove spaces from the sheet name and store name I have listed in column a of the summary sheet. How can I correct the formula so that I can keep spaces in the name?

[hoyasaxa215]

Formulas in excel get pretty tricky when you try to incorporate spaces. I'm honestly not sure how to make it work with spaces. You might be better off using a "_" instead of a space when running the formulas. Afterwards, if you want, you can hide that reference cell or just paste values and replace the "_" with a space " ". Maybe someone else knows a way to use references with spaces.

[sktneer]

If you have spaces in the Sheet Names, the formula would be like this (referring the attached workbook in post#2)

Please Login or Register  to view this content.

[AlannaBBB]

Thank you, that works perfectly!

[sktneer]

You're welcome. Thanks for the feedback.
If that takes care of your question, please mark your thread as Solved by selecting the Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.