I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2,D4)) )

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($D$2,D 4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?

If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4)))

Secret Squirrel wrote:
I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2,D4)) )

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($D$2,D 4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:

If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4)))

Secret Squirrel wrote:
I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2,D4)) )

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($D$2,D 4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF(L4:P4, $F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:
Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:


If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4)))

Secret Squirrel wrote:

I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2,D 4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($D$ 2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:

You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF(L4:P4, $F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:
Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:


If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4)))

Secret Squirrel wrote:

I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2,D 4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($D$ 2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

You're welcome! Thanks for letting me know that it worked.

Secret Squirrel wrote:
Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:


You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF(L4:P4, $F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:

Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:



If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4)))

Secret Squirrel wrote:


I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2 ,D4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($ D$2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Debra,

After reviewing my setup I have two more questions for you.

1. In that same formula that you helped me with I now want to add another
search function like the ending part (ISNUMBER(SEARCH($D$2,D4). I want to add
another search using B4. How would I add another to that portion of the
formula?

2. Also, In the cells that I use to filter/search my table (D2, E2, F2, G2)
can I put a pull down menu so the users can select their choice from a list?
How would I do this with my current setup?

Thanks!
SS

"Debra Dalgleish" wrote:

You're welcome! Thanks for letting me know that it worked.

Secret Squirrel wrote:
Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:


You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF(L4:P4, $F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:

Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:



If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4)))

Secret Squirrel wrote:


I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2 ,D4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($ D$2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Deb,

Me again! ;-)

Just running through the formula you fixed for me earlier and I think it
might not be working how I want it to. When I added those 2 extra filters,
F2, G2 it will only work on those filters when I use filter 1, E2. Maybe I
explained it the wrong way earlier. What I want to be able to do is make a
selection in E2 or F2 or G2 without using all three at the same time. Sort of
like the AND/OR in the beginning of the formula. That way I can use all 3 at
the same time or any of them individually without being dependent on each
other. I hope this makes better sense.

Also, I was able to create my pull down lists so disregard that from my
other response.

I do however still need to add another ISNUMBER/SEARCH formula like I
mentioned in my other post.

Sorry for all the confusion. I'm a little new at this type of filtering.

Thanks!
SS

"Debra Dalgleish" wrote:

You're welcome! Thanks for letting me know that it worked.

Secret Squirrel wrote:
Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:


You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF(L4:P4, $F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:

Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:



If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4)))

Secret Squirrel wrote:


I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D$2 ,D4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH($ D$2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I think you're trying to do too much in one formula. You could separate
the criteria into two formulas, in adjacent cells. In one formula, check
for E2, F2 and G2. For example:

=OR(IF($E$2<"",COUNTIF(L4:P4,$E$2),TRUE),IF($F$2< "",
COUNTIF(L4:P4,$F$2),TRUE), IF($G$2<"",COUNTIF(L4:P4,$G$2),TRUE))

In the next cell, check B2 and D2. For example:

=AND(ISNUMBER(SEARCH($B$2,C4)),ISNUMBER(SEARCH($D$ 2,D4)))

When running the Advanced Filter, select the two cells with formulas,
and the two blank cells above them. Because the formulas are in the same
row in the criteria range, they're treated as AND criteria. If they were
on different rows, they'd be OR criteria.

Secret Squirrel wrote:
Hi Deb,

Me again! ;-)

Just running through the formula you fixed for me earlier and I think it
might not be working how I want it to. When I added those 2 extra filters,
F2, G2 it will only work on those filters when I use filter 1, E2. Maybe I
explained it the wrong way earlier. What I want to be able to do is make a
selection in E2 or F2 or G2 without using all three at the same time. Sort of
like the AND/OR in the beginning of the formula. That way I can use all 3 at
the same time or any of them individually without being dependent on each
other. I hope this makes better sense.

Also, I was able to create my pull down lists so disregard that from my
other response.

I do however still need to add another ISNUMBER/SEARCH formula like I
mentioned in my other post.

Sorry for all the confusion. I'm a little new at this type of filtering.

Thanks!
SS

"Debra Dalgleish" wrote:


You're welcome! Thanks for letting me know that it worked.

Secret Squirrel wrote:

Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:



You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF(L4:P4, $F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:


Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:




If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4)))

Secret Squirrel wrote:



I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D $2,D4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH ($D$2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Deb,
Thanks again for your help but I'm still having a problem. I did what you
said and separated the code but only the B2/D2 formula is working. The others
will not filter at all. I checked the formula twice and it's correct the way
you wrote it. Out of these 5 filters I want to be able to use 1 or all or any
combination at all. Let me know if I'm doing something wrong or missing
something.
Thanks
SS

"Debra Dalgleish" wrote:

I think you're trying to do too much in one formula. You could separate
the criteria into two formulas, in adjacent cells. In one formula, check
for E2, F2 and G2. For example:

=OR(IF($E$2<"",COUNTIF(L4:P4,$E$2),TRUE),IF($F$2< "",
COUNTIF(L4:P4,$F$2),TRUE), IF($G$2<"",COUNTIF(L4:P4,$G$2),TRUE))

In the next cell, check B2 and D2. For example:

=AND(ISNUMBER(SEARCH($B$2,C4)),ISNUMBER(SEARCH($D$ 2,D4)))

When running the Advanced Filter, select the two cells with formulas,
and the two blank cells above them. Because the formulas are in the same
row in the criteria range, they're treated as AND criteria. If they were
on different rows, they'd be OR criteria.

Secret Squirrel wrote:
Hi Deb,

Me again! ;-)

Just running through the formula you fixed for me earlier and I think it
might not be working how I want it to. When I added those 2 extra filters,
F2, G2 it will only work on those filters when I use filter 1, E2. Maybe I
explained it the wrong way earlier. What I want to be able to do is make a
selection in E2 or F2 or G2 without using all three at the same time. Sort of
like the AND/OR in the beginning of the formula. That way I can use all 3 at
the same time or any of them individually without being dependent on each
other. I hope this makes better sense.

Also, I was able to create my pull down lists so disregard that from my
other response.

I do however still need to add another ISNUMBER/SEARCH formula like I
mentioned in my other post.

Sorry for all the confusion. I'm a little new at this type of filtering.

Thanks!
SS

"Debra Dalgleish" wrote:


You're welcome! Thanks for letting me know that it worked.

Secret Squirrel wrote:

Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:



You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF(L4:P4, $F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:


Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:




If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUMBER(SEAR CH($D$2,D4)))

Secret Squirrel wrote:



I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH($D $2,D4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEARCH ($D$2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

The formulas worked in my test, but maybe I'm misunderstanding how you
want it to work. If every filter is an OR, each should be on a separate
row in the criteria range.

Secret Squirrel wrote:
Hi Deb,
Thanks again for your help but I'm still having a problem. I did what you
said and separated the code but only the B2/D2 formula is working. The others
will not filter at all. I checked the formula twice and it's correct the way
you wrote it. Out of these 5 filters I want to be able to use 1 or all or any
combination at all. Let me know if I'm doing something wrong or missing
something.
Thanks
SS

"Debra Dalgleish" wrote:


I think you're trying to do too much in one formula. You could separate
the criteria into two formulas, in adjacent cells. In one formula, check
for E2, F2 and G2. For example:

=OR(IF($E$2<"",COUNTIF(L4:P4,$E$2),TRUE),IF($F$2< "",
COUNTIF(L4:P4,$F$2),TRUE), IF($G$2<"",COUNTIF(L4:P4,$G$2),TRUE))

In the next cell, check B2 and D2. For example:

=AND(ISNUMBER(SEARCH($B$2,C4)),ISNUMBER(SEARCH($D$ 2,D4)))

When running the Advanced Filter, select the two cells with formulas,
and the two blank cells above them. Because the formulas are in the same
row in the criteria range, they're treated as AND criteria. If they were
on different rows, they'd be OR criteria.

Secret Squirrel wrote:

Hi Deb,

Me again! ;-)

Just running through the formula you fixed for me earlier and I think it
might not be working how I want it to. When I added those 2 extra filters,
F2, G2 it will only work on those filters when I use filter 1, E2. Maybe I
explained it the wrong way earlier. What I want to be able to do is make a
selection in E2 or F2 or G2 without using all three at the same time. Sort of
like the AND/OR in the beginning of the formula. That way I can use all 3 at
the same time or any of them individually without being dependent on each
other. I hope this makes better sense.

Also, I was able to create my pull down lists so disregard that from my
other response.

I do however still need to add another ISNUMBER/SEARCH formula like I
mentioned in my other post.

Sorry for all the confusion. I'm a little new at this type of filtering.

Thanks!
SS

"Debra Dalgleish" wrote:



You're welcome! Thanks for letting me know that it worked.

Secret Squirrel wrote:


Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:




You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF( L4:P4,$F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:



Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:





If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUM BER(SEARCH($D$2,D4)))

Secret Squirrel wrote:




I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH( $D$2,D4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEAR CH($D$2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Deb,
I was able to get it working and here's what I did. I made a second criteria
cell with the following:

=AND(ISNUMBER(SEARCH($C$2,C4)),ISNUMBER(SEARCH($D$ 2,D4)))

The first criteria cell looks like this:

=AND(OR($E$2="",COUNTIF(L4:AO4,$E$2)),AND(OR($F$2= "",COUNTIF(L4:AO4,$F$2)),AND(OR($G$2="",COUNTIF(L4 :AO4,$G$2)),AND(OR($H$2="",COUNTIF(L4:AO4,$H$2)),A ND(OR($I$2="",COUNTIF(L4:AO4,$I$2)),AND(OR($J$2="" ,COUNTIF(L4:AO4,$J$2))))))))

Now when I use any or all of these 8 filters I'm able to filter my info
properly. Is this the correct way to do it? It's working but I want to be
sure it's the smart and easy way.

SS

"Debra Dalgleish" wrote:

The formulas worked in my test, but maybe I'm misunderstanding how you
want it to work. If every filter is an OR, each should be on a separate
row in the criteria range.

Secret Squirrel wrote:
Hi Deb,
Thanks again for your help but I'm still having a problem. I did what you
said and separated the code but only the B2/D2 formula is working. The others
will not filter at all. I checked the formula twice and it's correct the way
you wrote it. Out of these 5 filters I want to be able to use 1 or all or any
combination at all. Let me know if I'm doing something wrong or missing
something.
Thanks
SS

"Debra Dalgleish" wrote:


I think you're trying to do too much in one formula. You could separate
the criteria into two formulas, in adjacent cells. In one formula, check
for E2, F2 and G2. For example:

=OR(IF($E$2<"",COUNTIF(L4:P4,$E$2),TRUE),IF($F$2< "",
COUNTIF(L4:P4,$F$2),TRUE), IF($G$2<"",COUNTIF(L4:P4,$G$2),TRUE))

In the next cell, check B2 and D2. For example:

=AND(ISNUMBER(SEARCH($B$2,C4)),ISNUMBER(SEARCH($D$ 2,D4)))

When running the Advanced Filter, select the two cells with formulas,
and the two blank cells above them. Because the formulas are in the same
row in the criteria range, they're treated as AND criteria. If they were
on different rows, they'd be OR criteria.

Secret Squirrel wrote:

Hi Deb,

Me again! ;-)

Just running through the formula you fixed for me earlier and I think it
might not be working how I want it to. When I added those 2 extra filters,
F2, G2 it will only work on those filters when I use filter 1, E2. Maybe I
explained it the wrong way earlier. What I want to be able to do is make a
selection in E2 or F2 or G2 without using all three at the same time. Sort of
like the AND/OR in the beginning of the formula. That way I can use all 3 at
the same time or any of them individually without being dependent on each
other. I hope this makes better sense.

Also, I was able to create my pull down lists so disregard that from my
other response.

I do however still need to add another ISNUMBER/SEARCH formula like I
mentioned in my other post.

Sorry for all the confusion. I'm a little new at this type of filtering.

Thanks!
SS

"Debra Dalgleish" wrote:



You're welcome! Thanks for letting me know that it worked.

Secret Squirrel wrote:


Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:




You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF( L4:P4,$F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:



Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:





If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUM BER(SEARCH($D$2,D4)))

Secret Squirrel wrote:




I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH( $D$2,D4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEAR CH($D$2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I'm not sure I follow you when you say each should be on a separate row in
the criteria range. You are correct when you say every filter is an OR
statement. They could be an AND combined with some of the other filters or
they could be an OR alone or together.

"Debra Dalgleish" wrote:

The formulas worked in my test, but maybe I'm misunderstanding how you
want it to work. If every filter is an OR, each should be on a separate
row in the criteria range.

Secret Squirrel wrote:
Hi Deb,
Thanks again for your help but I'm still having a problem. I did what you
said and separated the code but only the B2/D2 formula is working. The others
will not filter at all. I checked the formula twice and it's correct the way
you wrote it. Out of these 5 filters I want to be able to use 1 or all or any
combination at all. Let me know if I'm doing something wrong or missing
something.
Thanks
SS

"Debra Dalgleish" wrote:


I think you're trying to do too much in one formula. You could separate
the criteria into two formulas, in adjacent cells. In one formula, check
for E2, F2 and G2. For example:

=OR(IF($E$2<"",COUNTIF(L4:P4,$E$2),TRUE),IF($F$2< "",
COUNTIF(L4:P4,$F$2),TRUE), IF($G$2<"",COUNTIF(L4:P4,$G$2),TRUE))

In the next cell, check B2 and D2. For example:

=AND(ISNUMBER(SEARCH($B$2,C4)),ISNUMBER(SEARCH($D$ 2,D4)))

When running the Advanced Filter, select the two cells with formulas,
and the two blank cells above them. Because the formulas are in the same
row in the criteria range, they're treated as AND criteria. If they were
on different rows, they'd be OR criteria.

Secret Squirrel wrote:

Hi Deb,

Me again! ;-)

Just running through the formula you fixed for me earlier and I think it
might not be working how I want it to. When I added those 2 extra filters,
F2, G2 it will only work on those filters when I use filter 1, E2. Maybe I
explained it the wrong way earlier. What I want to be able to do is make a
selection in E2 or F2 or G2 without using all three at the same time. Sort of
like the AND/OR in the beginning of the formula. That way I can use all 3 at
the same time or any of them individually without being dependent on each
other. I hope this makes better sense.

Also, I was able to create my pull down lists so disregard that from my
other response.

I do however still need to add another ISNUMBER/SEARCH formula like I
mentioned in my other post.

Sorry for all the confusion. I'm a little new at this type of filtering.

Thanks!
SS

"Debra Dalgleish" wrote:



You're welcome! Thanks for letting me know that it worked.

Secret Squirrel wrote:


Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:




You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTIF( L4:P4,$F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:



Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:





If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISNUM BER(SEARCH($D$2,D4)))

Secret Squirrel wrote:




I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARCH( $D$2,D4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SEAR CH($D$2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I'm just glad you got the formula working! If every criteria was an OR,
you could have put them each on a separate row. But it sounds like you
got the AND/OR combination that you needed, and as long as it's working,
I would just leave it as is.

Secret Squirrel wrote:
I'm not sure I follow you when you say each should be on a separate row in
the criteria range. You are correct when you say every filter is an OR
statement. They could be an AND combined with some of the other filters or
they could be an OR alone or together.

"Debra Dalgleish" wrote:


The formulas worked in my test, but maybe I'm misunderstanding how you
want it to work. If every filter is an OR, each should be on a separate
row in the criteria range.

Secret Squirrel wrote:

Hi Deb,
Thanks again for your help but I'm still having a problem. I did what you
said and separated the code but only the B2/D2 formula is working. The others
will not filter at all. I checked the formula twice and it's correct the way
you wrote it. Out of these 5 filters I want to be able to use 1 or all or any
combination at all. Let me know if I'm doing something wrong or missing
something.
Thanks
SS

"Debra Dalgleish" wrote:



I think you're trying to do too much in one formula. You could separate
the criteria into two formulas, in adjacent cells. In one formula, check
for E2, F2 and G2. For example:

=OR(IF($E$2<"",COUNTIF(L4:P4,$E$2),TRUE),IF($F$2< "",
COUNTIF(L4:P4,$F$2),TRUE), IF($G$2<"",COUNTIF(L4:P4,$G$2),TRUE))

In the next cell, check B2 and D2. For example:

=AND(ISNUMBER(SEARCH($B$2,C4)),ISNUMBER(SEARCH($D$ 2,D4)))

When running the Advanced Filter, select the two cells with formulas,
and the two blank cells above them. Because the formulas are in the same
row in the criteria range, they're treated as AND criteria. If they were
on different rows, they'd be OR criteria.

Secret Squirrel wrote:


Hi Deb,

Me again! ;-)

Just running through the formula you fixed for me earlier and I think it
might not be working how I want it to. When I added those 2 extra filters,
F2, G2 it will only work on those filters when I use filter 1, E2. Maybe I
explained it the wrong way earlier. What I want to be able to do is make a
selection in E2 or F2 or G2 without using all three at the same time. Sort of
like the AND/OR in the beginning of the formula. That way I can use all 3 at
the same time or any of them individually without being dependent on each
other. I hope this makes better sense.

Also, I was able to create my pull down lists so disregard that from my
other response.

I do however still need to add another ISNUMBER/SEARCH formula like I
mentioned in my other post.

Sorry for all the confusion. I'm a little new at this type of filtering.

Thanks!
SS

"Debra Dalgleish" wrote:




You're welcome! Thanks for letting me know that it worked.

Secret Squirrel wrote:



Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:





You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTI F(L4:P4,$F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:




Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:






If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISN UMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:





I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARC H($D$2,D4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SE ARCH($D$2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I agree, if it's not broken, don't fix it! Thanks again for helping me with
this. I greatly appreciate your help. You opened my mind a little so I could
see the light! Thanks again!

SS

"Debra Dalgleish" wrote:

I'm just glad you got the formula working! If every criteria was an OR,
you could have put them each on a separate row. But it sounds like you
got the AND/OR combination that you needed, and as long as it's working,
I would just leave it as is.

Secret Squirrel wrote:
I'm not sure I follow you when you say each should be on a separate row in
the criteria range. You are correct when you say every filter is an OR
statement. They could be an AND combined with some of the other filters or
they could be an OR alone or together.

"Debra Dalgleish" wrote:


The formulas worked in my test, but maybe I'm misunderstanding how you
want it to work. If every filter is an OR, each should be on a separate
row in the criteria range.

Secret Squirrel wrote:

Hi Deb,
Thanks again for your help but I'm still having a problem. I did what you
said and separated the code but only the B2/D2 formula is working. The others
will not filter at all. I checked the formula twice and it's correct the way
you wrote it. Out of these 5 filters I want to be able to use 1 or all or any
combination at all. Let me know if I'm doing something wrong or missing
something.
Thanks
SS

"Debra Dalgleish" wrote:



I think you're trying to do too much in one formula. You could separate
the criteria into two formulas, in adjacent cells. In one formula, check
for E2, F2 and G2. For example:

=OR(IF($E$2<"",COUNTIF(L4:P4,$E$2),TRUE),IF($F$2< "",
COUNTIF(L4:P4,$F$2),TRUE), IF($G$2<"",COUNTIF(L4:P4,$G$2),TRUE))

In the next cell, check B2 and D2. For example:

=AND(ISNUMBER(SEARCH($B$2,C4)),ISNUMBER(SEARCH($D$ 2,D4)))

When running the Advanced Filter, select the two cells with formulas,
and the two blank cells above them. Because the formulas are in the same
row in the criteria range, they're treated as AND criteria. If they were
on different rows, they'd be OR criteria.

Secret Squirrel wrote:


Hi Deb,

Me again! ;-)

Just running through the formula you fixed for me earlier and I think it
might not be working how I want it to. When I added those 2 extra filters,
F2, G2 it will only work on those filters when I use filter 1, E2. Maybe I
explained it the wrong way earlier. What I want to be able to do is make a
selection in E2 or F2 or G2 without using all three at the same time. Sort of
like the AND/OR in the beginning of the formula. That way I can use all 3 at
the same time or any of them individually without being dependent on each
other. I hope this makes better sense.

Also, I was able to create my pull down lists so disregard that from my
other response.

I do however still need to add another ISNUMBER/SEARCH formula like I
mentioned in my other post.

Sorry for all the confusion. I'm a little new at this type of filtering.

Thanks!
SS

"Debra Dalgleish" wrote:




You're welcome! Thanks for letting me know that it worked.

Secret Squirrel wrote:



Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:





You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUNTI F(L4:P4,$F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:




Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:






If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),ISN UMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:





I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEARC H($D$2,D4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER(SE ARCH($D$2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

You're welcome!

Secret Squirrel wrote:
I agree, if it's not broken, don't fix it! Thanks again for helping me with
this. I greatly appreciate your help. You opened my mind a little so I could
see the light! Thanks again!

SS

"Debra Dalgleish" wrote:


I'm just glad you got the formula working! If every criteria was an OR,
you could have put them each on a separate row. But it sounds like you
got the AND/OR combination that you needed, and as long as it's working,
I would just leave it as is.

Secret Squirrel wrote:

I'm not sure I follow you when you say each should be on a separate row in
the criteria range. You are correct when you say every filter is an OR
statement. They could be an AND combined with some of the other filters or
they could be an OR alone or together.

"Debra Dalgleish" wrote:



The formulas worked in my test, but maybe I'm misunderstanding how you
want it to work. If every filter is an OR, each should be on a separate
row in the criteria range.

Secret Squirrel wrote:


Hi Deb,
Thanks again for your help but I'm still having a problem. I did what you
said and separated the code but only the B2/D2 formula is working. The others
will not filter at all. I checked the formula twice and it's correct the way
you wrote it. Out of these 5 filters I want to be able to use 1 or all or any
combination at all. Let me know if I'm doing something wrong or missing
something.
Thanks
SS

"Debra Dalgleish" wrote:




I think you're trying to do too much in one formula. You could separate
the criteria into two formulas, in adjacent cells. In one formula, check
for E2, F2 and G2. For example:

=OR(IF($E$2<"",COUNTIF(L4:P4,$E$2),TRUE),IF ($F$2<"",
COUNTIF(L4:P4,$F$2),TRUE), IF($G$2<"",COUNTIF(L4:P4,$G$2),TRUE))

In the next cell, check B2 and D2. For example:

=AND(ISNUMBER(SEARCH($B$2,C4)),ISNUMBER(SEARCH($D$ 2,D4)))

When running the Advanced Filter, select the two cells with formulas,
and the two blank cells above them. Because the formulas are in the same
row in the criteria range, they're treated as AND criteria. If they were
on different rows, they'd be OR criteria.

Secret Squirrel wrote:



Hi Deb,

Me again! ;-)

Just running through the formula you fixed for me earlier and I think it
might not be working how I want it to. When I added those 2 extra filters,
F2, G2 it will only work on those filters when I use filter 1, E2. Maybe I
explained it the wrong way earlier. What I want to be able to do is make a
selection in E2 or F2 or G2 without using all three at the same time. Sort of
like the AND/OR in the beginning of the formula. That way I can use all 3 at
the same time or any of them individually without being dependent on each
other. I hope this makes better sense.

Also, I was able to create my pull down lists so disregard that from my
other response.

I do however still need to add another ISNUMBER/SEARCH formula like I
mentioned in my other post.

Sorry for all the confusion. I'm a little new at this type of filtering.

Thanks!
SS

"Debra Dalgleish" wrote:





You're welcome! Thanks for letting me know that it worked.

Secret Squirrel wrote:




Thanks Debra! That worked perfectly!

"Debra Dalgleish" wrote:






You could include a COUNTIF for each criteria cell:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2),COUN TIF(L4:P4,$F$2),
COUNTIF(L4:P4,$G$2)),ISNUMBER(SEARCH($D$ 2,D4)))

Secret Squirrel wrote:





Hi Debra,
Thank you, that worked perfectly. Now a followup question:

I currently have two ways of filtering my data, first from cell D2, and then
an optional one in E2. How would I re-write that formula to add a third, or
even forth filter? Say I wanted to use F4 & G4 as additional filters. These
two new filters would also use the same criteria range L4:P4.

Thanks!

"Debra Dalgleish" wrote:







If you're looking for the value in E2 in any cell in L4:P4, you could
use this formula:

=AND(OR($E$2="",COUNTIF(L4:P4,$E$2)),I SNUMBER(SEARCH($D$2,D4)))

Secret Squirrel wrote:






I'm using an Advanced Filter to filter my data but I can't figure out how to
set up my criteria range. I currently have this as my criteria range:

=AND(OR($E$2="",L4=$E$2),ISNUMBER(SEA RCH($D$2,D4)))

Column L is one of the columns I want to filter my data by but I also want
to be able to filter other columns as well. Columns L thru P all have data
that I need to filter and I though just simply changing the above to this
would work:

=AND(OR($E$2="",L4:P4=$E$2),ISNUMBER( SEARCH($D$2,D4)))

But it didn't. How do I re-write this formula to be able to lookup up the
data in multiple columns?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html