I've searched the forums and can't find the solution to my problem, though it may be there in a combination of multiple problems.
I have multiple worksheets in my workbook. Some of the worksheets are summary pages, data parameter pages with helper cells (etc), but most worksheets have the worksheet name derived from the property name it represents.
I need to find the best way to search every worksheet and create a list of all the properties and the location/region the property is in. (ie, Towering Heights is in New York state).
Each property worksheet has static data with labels in Column A and the data in Column B. (ie- A1=Property Name B1=Towering Heights A2=Location B2=New York).
I tried to make a pivot table with Multiple ranges selecting every property sheet name individually with cell ranges A1:B2, but I couldn't get it to "sort" by the states and then the Property names within that state.
I don't know visual basic, but looked at this page http://www.rondebruin.nl/tips.htm and think perhaps there is something there that could search each worksheet and pull those two cells B1 and B2 into a summary page with all the properties. I could then sort that list by the location the properties are in.
Is there a way to search every worksheet and if the worksheet name itself matches cell A2 then consolidate a list of properties based off the location?
If my question isn't clear, I apologize and I will try to clarify more.
Thank you for any direction.
-Maku
This post by Luke http://chandoo.org/wp/2011/11/18/formula-forensics-003/ is very close to what I need to do, but is within a single page. I don't know how to use that to look in every worksheet. If I could, then I could tell it to look in every worksheet, and if Cell A2 has "Location" then list the location.
I have multiple worksheets in my workbook. Some of the worksheets are summary pages, data parameter pages with helper cells (etc), but most worksheets have the worksheet name derived from the property name it represents.
I need to find the best way to search every worksheet and create a list of all the properties and the location/region the property is in. (ie, Towering Heights is in New York state).
Each property worksheet has static data with labels in Column A and the data in Column B. (ie- A1=Property Name B1=Towering Heights A2=Location B2=New York).
I tried to make a pivot table with Multiple ranges selecting every property sheet name individually with cell ranges A1:B2, but I couldn't get it to "sort" by the states and then the Property names within that state.
I don't know visual basic, but looked at this page http://www.rondebruin.nl/tips.htm and think perhaps there is something there that could search each worksheet and pull those two cells B1 and B2 into a summary page with all the properties. I could then sort that list by the location the properties are in.
Is there a way to search every worksheet and if the worksheet name itself matches cell A2 then consolidate a list of properties based off the location?
If my question isn't clear, I apologize and I will try to clarify more.
Thank you for any direction.
-Maku
This post by Luke http://chandoo.org/wp/2011/11/18/formula-forensics-003/ is very close to what I need to do, but is within a single page. I don't know how to use that to look in every worksheet. If I could, then I could tell it to look in every worksheet, and if Cell A2 has "Location" then list the location.