Autofilter Function

[TortolaChris]

I have two rows of data. On the first row I have salesman names and on the second row names of towns. I would like to be able to compile a list of the towns that specific salesmen look after.

The problem is that salesmen look after multiple towns so I don't think array formulae would work - would they?

I want to refer to the resultant data in a drop down menu in another sheet so I think I need to write formulae rather than use autofilter.

I hope this makes some sense - thanks for any help

Chris

 

[xlhelp]

Chris,

You can use autofilter, it takes a little bit of work, but it will do what you want. Once you have filtered the name of the sales person, you will have towns for that person. Then all you need to do is highlight the range, go to Edit, GoTo, click Special and choose visible cells only. Copy and paste wherever you like.

If you really want to use the formula, one way would be to enter the name of the person a second sheet (A2) and then put in a formula
=if($range=a2, B2,"&quot

But there also you need to input the name.

 

[TortolaChris]

Sorry - I am really bad at describing what I need. I don't think I can use autofilter because I want users to select a name from a created list box and then select a town from another list box - the options for which have been generated from what they selected in the first box. Really confusing I know.

 

[VulcanJedi]

I think what you want, is a pivot table, I recently became acquainted with this tool and I resent that since I think its applicable to alot of things like your situation I think you should give it a try. Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[klmorgan]

You could also use an advanced filter

Are trying to achieve
a) what towns are covered by which salesman?

b) what salesman covers what town?

Regards

Keith