Pulling Data From Multiple Sheets Into A Summary With Variable Criteria

**stinkyd** · 05-28-2010, 09:20 AM

I have 10 worksheets of market data from different markets and I want to pull some of this data into a summary sheet for 2 markets, that are chosen by the user. The summary sheet is in a presentation format so the data needs to be pulled in into defined template.

How do I accomplish this? I've tried using cell references, but I can't make the worksheet reference within the cell variable with the user selection.

**antoka05** · 05-28-2010, 10:30 AM

You should use the Indirect formula.
I.e. if you have your sheets named 'Market1', 'Market2'... 'Market10', and so on and the chosen market is 1 or 2 ... or 10 (choosen value is in Range A2) and you need to put in range 'A10' of summary sheet range 'B5' of chosen market you can use this formula in Range A10:

=INDIRECT("Market" & A2 & "!B5" ; 1)

Regards,
Antonio

**stinkyd** · 05-28-2010, 03:18 PM

Thanks for the response, but I've tried to use the indrect reference formula and it's not working. To break down the issue I have the following setup:

Summary Worksheet
User inputs which market they want to look at in cell Q3. The input references the market name, which is also the Worksheet name "San Diego".

The Summary Worksheet has several graphs on it used to compare multiple markets that are picked by the user, the graphs reference a single source on the "Summary Data Worksheet"

Summary Data Worksheet
This data is pulled from the individual market worksheets (user defined on the Summary Worksheet) to be used by the graphs on the Summary Worksheet.

This is where I need the formula or programming to be able to pull in the correct market for the data to be used by the graphs on the Summary Worksheet.

Market Worksheets
Our analyst does their research and inputs data directly into each market tab "San Diego", "LA", "Orange County", etc

Please advise, your help is greatly appreciated. Thanks.

**stinkyd** · 05-28-2010, 03:44 PM

Nevermind, got your formula to work. Thank you. Forza Roma.

**antoka05** · 05-28-2010, 07:42 PM

I'm glad it runs fine... many thanks for forza Roma!

Have a nice time.

Regards,
Antonio

**Marushka Pinto** · 10-04-2015, 03:44 PM

Hi There, This is my first post and hopefully the right forum.

I need help with either a vba / formula which will help in consolidating data based on multiple criteria.

I have attached the document which contains the following:

1. Top Risks Landing Page :Pick up All the risks, only from the sheets highlighted in Green, based on the "Risk Category" or "Rating Column" and consolidate them into the "Top Risks Landing Page" in this order

•Region ( Apac,Americas,Emea,Global)
•Business Unit (A, B C)
•Risk Category
•Rating

In addition to the displaying all the RAG items, can there be an option of selecting the month and the status and based on the selection, the data should be displayed.Have also mentioned this in the attachment.

2. Regional View : It should then pick up the risks Region wise from the "Top Risks Landing Page" and put them in the 3 sheets titled " Americas", "APAC" , EMEA" respectively in this order

•Monthwise (Or give an option to select the month, and based on the selection display the data in the order below)
•Region ( Apac,Americas,Emea,Global)
•Business Unit (A, B C)
•Risk Category
•Rating

Note : If the Region column has " Global" mentioned , the issue should populate in all three regions.

3. Top Risks-Red Items : Next it should pick up only the Red rated items from the "Top Risks Landing Page" and consolidate them into the "Top Risks-Red Items" sheet in the same order as in point 1.

4. Top ICSS Risks : The "Top ICSS Risks" is the Final Sheet which should capture data from the 4 sheets namely :

i) "Top Risks-Red Items"

ii) "Macro View"

iii) "Regulatory View"

iv) "Forward View"

I have written some comments and notations in the sheets for reference

If you think there is a better way of doing this, do let me know.

Its kind of an urgent requirement, so any help would be appreaciated.

Thank You.

**FDibbins** · 10-04-2015, 03:51 PM

Marushka welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**Marushka Pinto** · 10-04-2015, 04:15 PM

Thank You Ford,

Apologies for not abiding with the rules.

Will start my own thread as suggested

Thanks again an am glad to have joined Excel Forum

Marushka