Hi All,

I have the follwing databse

OFFICE SALES COST MARGIN
100 1000 800 200
101 1200 900 300
102 800 700 100
103 1400 1200 200
.... .... .... ...
200 .... .... ...

Can I Use filter / advanced filter to select data based
on a few offices?

for eg. if I want to get data related to office
100,150,175,200 ?

I want to be able to change these offices. I want to
select several offices say 50, 75 etc at a time and these
office no's may change often.

How can I achieve this using code?

TIA
Soniya

What I do is insert a new column just for filtering purposes.

So to the left of Office column, I have a column labelled FilterBy.
Then put an x in that column next to 100, 150, 175, 200
Highlight the entire FilterBy column and select menu: Data | Filter |
Autofilter

then filter on x


You can get even smarter with it.
Some offices are x and others are y then your FilterBy column can look like
(for example):

x
x
y

xy
y

From the autofilter dropdown, select (Custom...)
Then choose Contains y


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Soniya" wrote in message
...
Hi All,

I have the follwing databse

OFFICE SALES COST MARGIN
100 1000 800 200
101 1200 900 300
102 800 700 100
103 1400 1200 200
... .... .... ...
200 .... .... ...

Can I Use filter / advanced filter to select data based
on a few offices?

for eg. if I want to get data related to office
100,150,175,200 ?

I want to be able to change these offices. I want to
select several offices say 50, 75 etc at a time and these
office no's may change often.

How can I achieve this using code?

TIA
Soniya

A few columns to the right of your database, or on a different
worksheet, create a list of the offices you want to filter. Use the same
heading as the Office column in the database.

Then, in a column adjacent to the database, add a heading, e.g. "Filter"
In the cell below, type: =IF(COUNTIF($G$1:$G$20,A2),"X","")
where the list of offices is in cell G1:G20
(you can include several blank cells at the end of this range,
so the list can grow)
Copy the formula down to the last row of data

Select a cell in the database, and choose DataFilterAutoFilter
From the dropdown list in the Filter column, select X

Soniya wrote:
Hi All,

I have the follwing databse

OFFICE SALES COST MARGIN
100 1000 800 200
101 1200 900 300
102 800 700 100
103 1400 1200 200
... .... .... ...
200 .... .... ...

Can I Use filter / advanced filter to select data based
on a few offices?

for eg. if I want to get data related to office
100,150,175,200 ?

I want to be able to change these offices. I want to
select several offices say 50, 75 etc at a time and these
office no's may change often.

How can I achieve this using code?

TIA
Soniya




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html