I was using the following formula when filtering a sheet of data:

=OR(B2=1,AND(OR(B2=2,B2=3),L2<0,K2=0,I2=ayearago ))

It filters all rows with 1 in column B and any other rows with 2 or 3 in
column B that meet the criteria L2<0,K2=0,I2=ayearago (where ayearago is a
named cell containing a date).

I now find myself needing to change this formula to filter for all rows with
1 in column B and any other rows with 2 or 3 in column B that meet the
criteria L2<0,K2=0,I2=ayearago and L2<0,K2=thirtydaysago,I2=ayearago.

I have tried but with no success so far.

Thanks in advance.

Gareth

Hi Gareth
If I understand the problem correctly, then
=AND(OR(B20,B2<=3),OR(K2=0,K2=thirtydaysago),L2< 0,I2=ayearago)

Regards

Roger Govier


Gareth wrote:
I was using the following formula when filtering a sheet of data:

=OR(B2=1,AND(OR(B2=2,B2=3),L2<0,K2=0,I2=ayearago ))

It filters all rows with 1 in column B and any other rows with 2 or 3 in
column B that meet the criteria L2<0,K2=0,I2=ayearago (where ayearago is a
named cell containing a date).

I now find myself needing to change this formula to filter for all rows with
1 in column B and any other rows with 2 or 3 in column B that meet the
criteria L2<0,K2=0,I2=ayearago and L2<0,K2=thirtydaysago,I2=ayearago.

I have tried but with no success so far.

Thanks in advance.

Gareth

Hi Gareth,

Staying with your original setup of the formula:

=OR(B2=1,AND(OR(B2=2,B2=3),L2<0,OR(K2=0,K2="thir tydaysago"),I2="ayearago"
))

Roger's formula rearranges it, but you need the B2=1, as B20 would include
values of 4 on unless I'm mistaken.

=AND(OR(B2=1,B2<=3),OR(K2=0,K2="thirtydaysago"),L 2<0,I2="ayearago")


CHORDially,
Art Farrell


"Gareth" wrote in message
...
I was using the following formula when filtering a sheet of data:

=OR(B2=1,AND(OR(B2=2,B2=3),L2<0,K2=0,I2=ayearago ))

It filters all rows with 1 in column B and any other rows with 2 or 3 in
column B that meet the criteria L2<0,K2=0,I2=ayearago (where ayearago is
a
named cell containing a date).

I now find myself needing to change this formula to filter for all rows
with
1 in column B and any other rows with 2 or 3 in column B that meet the
criteria L2<0,K2=0,I2=ayearago and L2<0,K2=thirtydaysago,I2=ayearago.

I have tried but with no success so far.

Thanks in advance.

Gareth

Hi Art

Nice catch.
You are quite right, my formula would have picked up any value in B2,
outside of the desired range.

Regards

Roger Govier


Art Farrell wrote:
Hi Gareth,

Staying with your original setup of the formula:

=OR(B2=1,AND(OR(B2=2,B2=3),L2<0,OR(K2=0,K2="thir tydaysago"),I2="ayearago"
))

Roger's formula rearranges it, but you need the B2=1, as B20 would include
values of 4 on unless I'm mistaken.

=AND(OR(B2=1,B2<=3),OR(K2=0,K2="thirtydaysago"),L 2<0,I2="ayearago")


CHORDially,
Art Farrell


"Gareth" wrote in message
...

I was using the following formula when filtering a sheet of data:

=OR(B2=1,AND(OR(B2=2,B2=3),L2<0,K2=0,I2=ayeara go))

It filters all rows with 1 in column B and any other rows with 2 or 3 in
column B that meet the criteria L2<0,K2=0,I2=ayearago (where ayearago is

a

named cell containing a date).

I now find myself needing to change this formula to filter for all rows

with

1 in column B and any other rows with 2 or 3 in column B that meet the
criteria L2<0,K2=0,I2=ayearago and L2<0,K2=thirtydaysago,I2=ayearago.

I have tried but with no success so far.

Thanks in advance.

Gareth