Create a criteria range (H1:H2) with a blank heading cell, and in the
cell below, use a formula that refers to your company list and cost
centre list. For example:
=AND(COUNTIF(Companies,F2),COUNTIF(CostCtrs,J2))
where the first company in the data is in cell F2, and the first cost
centre is in J2.
(E-Mail Removed) wrote:
> On May 8, 1:44 pm, dean.bru...@lion-nathan.com.au wrote:
>
>>Hi,
>>
>>I have used the following Advanced Filter using named ranges for the
>>List, Criteria and CopyToRange. The code runs but only half of the
>>criteria seems to work. I try the same Advanced filter manually with
>>the same result. What do I need to have multiple criteria work. The
>>criteria below works only for the Cost Centre field but not the
>>Company field. The Company field returns all company numbers but I
>>want only the one. Appreciate any help. Cheers, Dean.
>>
>>CODE:
>>
>>Sheets("Sheet1").Select
>> Range("A1").Select
>> Sheets("Detail").Range("Database").AdvancedFilter
>>Action:=xlFilterCopy, _
>> CriteriaRange:=Sheets("Distribution").Range("H1:I17"),
>>CopyToRange:=Range( _
>> "A1:I1"), Unique:=False
>>
>>CRITERIA
>>
>>Company Cost Centre
>>2100 4000
>> 4010
>> 4021
>> 4022
>> 4023
>> 4025
>> 4026
>
>
> Hi I have now got it to work by putting 2100 with each cost centre
> however I have other filters to do where the Company list is 4 records
> and the Cost Centre list is 12. Do I therefore need to create 48
> combos in the criteria list or can the criteria statement be
> structured differently.
>
> NEW CRITERIA EXAMPLE:
> Company Cost Centre
> 2100 7000
> 2200 7020
> 2300 7035
> 2400 7040
> 7045
> 7055
> 7065
> 7070
> 7075
> 7080
> 7115
> 7120
>
--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html
Similar Threads
