Advanced Filter Criteria 2

[Gavona]

I am using advanced filter and want to filter for all values that both start and end with a 1. These are actually 6 character numbers rather than text. So I want to display 123451 but not 123456 or 223451.

I've tried various things but when I add these criteria to the other ones already in place (on the same row in the criteria range) the criteria results in a longer list than if I have just the one criteria (I don't think that this should happen!)

I am actually using VBA so my criteria range is
Range(c.Value).CurrentRegion however I have also tested manually applying advancedfilter with the same results.

I am actually applying 1 of 8 different criteria sets, processing the filtered data and then applying the next criteria set - using code. My approach is working for the 7 other sets so I think it is just my inability to work out the criteria...

Because of the somewhat dynamic nature of the source data I would really rather refer to named ranges/field names than specific cell references.

I have tried
Fieldname1 Fieldname2
fred =1????1


Fieldname1
Fred =LEFT(TEXT($AD$10,"######"),1)=1
(I know that this only meets half my requirement but....)

Fieldname1
Fred =AND(LEFT(Data!$AD$10,1)="1",RIGHT(Data!$AD$10,1)="1")

Thanks,

Gavin

 

[ilses]

Gavin,

This =AND(LEFT(Data!$AD$10,1)="1",RIGHT(Data!$AD$10,1)="1")
works if you change the references to relative references, i.e. without the $$. Plus, if you use a formula as criterium the column in the criteria range should not have a header (leave it blank) or the header should be something that is not used in the database range headers,

HTH

Ilse

 

[byundt]

Gavin,
Here is a shorter version of Ilse's formula:
=LEFT(AD10)&RIGHT(AD10)="11"

In worksheet formulas, you don't need the second parameter in LEFT and RIGHT if you only want one character.
Brad

 

[Gavona]

Thanks Isle, Brad. With the confidence you gave me that I was doing the right thing I managed to sort the problem.

My error was actually in referencing the field heading rather than the first data item - I should have used row 11!
(The dollar signs in my post were misleading - I actually only had the columns fixed.)

I think Brad's idea is neat but I think it is clearer to specify the number of characters explicitly - especially assomeone else might have to pick up the workbook in the future and modify it for changed circumstances. The concatenation idea is good because there are plenty around who may get confused with And but who regularly add text fields together.



Thanks,

Gavin