Drop down list formula to change multiple cells

Occasional Visitor

I created a drop down list of a few cities in Upstate, NY like: Buffalo, Rochester, & Syracuse and I have several rows of data that is listed as percent of revenue. I am trying to find a way so that when I pick a city like (Buffalo) the % of revenue or other data will auto populate in the indicated cells.


For example: Buffalo has 8,000 patients, Syracuse has 3,000 patients, and Rochester has 5,000 patients and in the Row designated to (Patients) I would like to be able to choose a city and have the number of patients data from another sheet to correspond correctly to the appropriate city. Or Buffalo has say 14.5% revenue increase, Rochester has 10% revenue increase and Syracuse has a 4% decrease, how could I show these changes via the drop down list?


My apologies for being long winded about this I do not know how else to explain the Excel formula that I am looking for. Any help would be greatly appreciated.


Thank you!

1 Reply

I understand that you need the properties of each city to be filled automatically whenever the cell selected.

This depends on what type of data source you have and what info you need.

But generally, you have to ways to try and go through:

If the required info is fixed, you can attach them to the source of the drop down list, then use VLookup function to populate the data.

This is the solution that I think fits here.

Otherwise, if the required info comes from another raw data sheet, as summary for example, you can use a custom SUMIFS function, or one of it's siblings (Countifs, Averageifs,...) depends on whatever you need.

I attached a simple sheet to explain the first solution.