Summarizing data for multiple locations based on drop down result

[muad'ebe]

I've developed a workbook to calculate pricing. Pricing for labor differs based on location - there are 4 locations. A location is chosen and then labor rates auto populate the worksheet(s) based on the chosen location. The location is a drop down list with results 1,2,3,4 in cell M3. (City1, City 2, City 3, City 4)

I can easily summarize the data for the selected location.

I would like to be able to summarize the data side by side for each of the 4 locations simultaneously, even though one specific location has been selected. So it would be the summary as if I chose each location separately, but simultaneously regardless of the selected location.

Is there a formula to collect summary data for locations 1, 2, 3, & 4 (cell M3) even though the pricing has been based on the actual (single) location selected in cell M3?

All labor rates populate the worksheet using vlookup based on the selected location.

Hope I was able to explain the problem clearly. Thank you in advance for any help you may be able to offer.

 

[muad'ebe]

I added a screenshot example to better illustrate the problem. In the screenshot the rate & burden are pulled into the table based on the value of cell M3. I would like to be able to have the summary populate with the total for each location (1-4) regardless of which location number is selected in M3. I would like to have a formula that will calculate and populate as if the M3 was a different location number to compare data for each location in the summary table regardless of selected location. Thanks for taking a look.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Summarizing data based "as if" a cell contained different number
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[muad'ebe]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Summarizing data based "as if" a cell contained different number
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Also posted same question here:

Summarizing data based "as if" a cell contained different number