0

I work in sales and cover a large territory. To manage the territory, I break it up into "Zones" (i.e. Philly / Pittsburgh / etc). My organization provides an excel file with every company I call on and a litany of sales information - this is Sheet 1 of my excel workbook. I've already inserted an extra column on Sheet 1 and labeled "Zone" to identify where each company will fall into.

To simplify the management within each zone, I want separate Sheets for each of the named zones. What I'd like to know is what formula I can enter on the sheet labeled "Philly" such that "if" the Zone Column on Sheet 1 = "Philly", then the entire row will map to the sheet labeled Philly.

Further, some zones have sub-zones (i.e Central PA) and so I'd like one sheet to search for any Sheet 1 Zone equal to Harrisburg, State College, Scranton, etc and bring them all to the one sheet named Central PA in the same fashion as above.

Thanks for your help!

Improve this question
2
  • Here at SuperUser, we usually include what we've tried in the questions. I think what you're looking for is VLOOKUP, have you tried that yet? A quick search could set you right up.
    – Wutnaut
    Apr 18, 2014 at 19:04
  • @Wutnaut is right as far as a function. IMO, that will take a lot of formula typing. You may want to consider pivot tables as well. Or Power Query maybe.
    – CharlieRB
    Apr 18, 2014 at 19:23

1 Answer 1

Reset to default
0

I would convert the Sheet1 range to an Excel Table (from the Insert Ribbon). Then you can use the Filter buttons on the column heading cells to select e.g. Zone = Philly, which will then only show you the Philly rows.

This approach is very simple to maintain and avoids formulas and duplicating data. A consolidated table is also a great source for charts, Pivot Tables, Power View etc.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .