adv filter wont accept criteria

D

daniel

I am trying to advance filter a spreadsheet containing housing information.
Namingly, one column is titled "lot type" with the corresponding data in that
column reading "Type 1" or Type 2,3 etc. I wish to sort the lots by my choice
of number from one to 11. nothing seems to work. I think perhaps I don't know
the correct code that excel will accept. Maybe an issue of text and numerical
data in the same cell? being brand new to filtering doesn't help either!
 
G

Gary''s Student

Consider using Autofilter. It will allow you to specify exactly what you want.
 
P

Peo Sjoblom

It's definitely an issue of text and numbers ,however you can use a formula
as the advanced filter criteria, assume the whole date including headers are
A6:D500 and the "lot type" is A:A500 with the different "Types" starting in
A7
Let us assume the criteria range is $H$1:$H$2, put the type number in G1 (1,
2, 3 etc)
Leave H1 blank and put this formula in H2

=--(TRIM(SUBSTITUTE(A7,"Type","")))=$G$1


apply the filter

so if you put 3 in G1 it will filter all Type 3 lots


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
 
Top