filtering questions.

[lezawang]

Hi
I have the table below. I want to use a filter to keep only employee of Marketing dept. After filtering dept column now I am only interested in this new look table

rank	name	dept	salary
2	mary1	marketing	99
4	sarah1	marketing	88
6	kim1	marketing	33

My questions:
1) How can I keep only this table? copy and paste? or you have a better idea?
2) The first column which is called Rank. I wanted to enter in the first row # 1 and then in the second row #2 and then highlight them and click on AutoFill. Thinking that would give me 1,2,3,4,5,...... but it does not. How can I do that (the filter is still on by the way)?

Thank you so much.


The original table

rank	name	dept	salary
1	jack1	hr	100
2	mary1	marketing	99
3	linda1	sales	77
4	sarah1	marketing	88
5	allen1	hr	55
6	kim1	marketing	33
7	li1	sales	22

 

[Fluff]

One option with a helper column.
In name Manager (on the formula tab) create a new name & call it IsVisible & in the Refers to box put
=GET.CELL(17,INDIRECT("rc",FALSE))

Then you can use it like

ZFluff.xlsm
	A	B	C	D	E
1	rank	name	dept	salary
2	1	jack1	hr	100	12.75
3	2	mary1	marketing	99	12.75
4	3	linda1	sales	77	12.75
5	4	sarah1	marketing	88	12.75
6	5	allen1	hr	55	12.75
7	6	kim1	marketing	33	12.75
8	7	li1	sales	22	12.75
Finals

Cell Formulas
Range	Formula
A2:A8	A2	=COUNTIF(E$2:E2,">0")
E2:E8	E2	=IsVisible

ZFluff.xlsm
	A	B	C	D	E
1	rank	name	dept	salary
3	1	mary1	marketing	99	12.75
5	2	sarah1	marketing	88	12.75
7	3	kim1	marketing	33	12.75
Finals

Cell Formulas
Range	Formula
A3,A5,A7	A3	=COUNTIF(E$2:E3,">0")
E3,E5,E7	E3	=IsVisible