exceptions in filtering

i have an advanced filter that i want to display unique values, but due to
cells being added later on, i have the filter on more cells than contents at
the moment, but its taking a blank cell as a unique value, which i dont want.

can you add an excpetion so that the cell must contain data or not empty to
then be filtered? i have looked on this website but found nothing -
http://office.microsoft.com/en-us/as...001781033.aspx

if not, is there another way to do this?

thanks

Jamie

Hi Jamie

Instead of defining a larger range with blanks to allow for later data
entry, create a Dynamic named range for your data and pass the named
range to Advanced Filter instead of your static range. That way, there
will always be data in the relevant fields and you won't have the
problems with blanks.

For help on setting up Dynamic Ranges take a look at Debra Dalgleish's
site
http://www.contextures.com/xlNames01.html#Dynamic

--
Regards

Roger Govier


"jamie" <[email protected]> wrote in message
news:[email protected]...
>i have an advanced filter that i want to display unique values, but due
>to
> cells being added later on, i have the filter on more cells than
> contents at
> the moment, but its taking a blank cell as a unique value, which i
> dont want.
>
> can you add an excpetion so that the cell must contain data or not
> empty to
> then be filtered? i have looked on this website but found nothing -
> http://office.microsoft.com/en-us/as...001781033.aspx
>
> if not, is there another way to do this?
>
> thanks
>
> Jamie

Roger

thanks for the info. i have completed the steps to create this range. how
do i pass the naed range through the advanced filter? looking at the
wizzard, i am unsure where to tell it to look at the dynamic range.

thanks

"Roger Govier" wrote:

> Hi Jamie
>
> Instead of defining a larger range with blanks to allow for later data
> entry, create a Dynamic named range for your data and pass the named
> range to Advanced Filter instead of your static range. That way, there
> will always be data in the relevant fields and you won't have the
> problems with blanks.
>
> For help on setting up Dynamic Ranges take a look at Debra Dalgleish's
> site
> http://www.contextures.com/xlNames01.html#Dynamic
>
> --
> Regards
>
> Roger Govier
>
>
> "jamie" <[email protected]> wrote in message
> news:[email protected]...
> >i have an advanced filter that i want to display unique values, but due
> >to
> > cells being added later on, i have the filter on more cells than
> > contents at
> > the moment, but its taking a blank cell as a unique value, which i
> > dont want.
> >
> > can you add an excpetion so that the cell must contain data or not
> > empty to
> > then be filtered? i have looked on this website but found nothing -
> > http://office.microsoft.com/en-us/as...001781033.aspx
> >
> > if not, is there another way to do this?
> >
> > thanks
> >
> > Jamie
>
>
>

Hi Jamie

Data>Filter>Advanced Filter>List Range enter
= myrange
(or whatever you have called it) rather than $A$1:$M$1000.
NOTE
You must put the = sign in front of the name range

--
Regards

Roger Govier


"jamie" <[email protected]> wrote in message
news:[email protected]...
> Roger
>
> thanks for the info. i have completed the steps to create this range.
> how
> do i pass the naed range through the advanced filter? looking at the
> wizzard, i am unsure where to tell it to look at the dynamic range.
>
> thanks
>
> "Roger Govier" wrote:
>
>> Hi Jamie
>>
>> Instead of defining a larger range with blanks to allow for later
>> data
>> entry, create a Dynamic named range for your data and pass the named
>> range to Advanced Filter instead of your static range. That way,
>> there
>> will always be data in the relevant fields and you won't have the
>> problems with blanks.
>>
>> For help on setting up Dynamic Ranges take a look at Debra
>> Dalgleish's
>> site
>> http://www.contextures.com/xlNames01.html#Dynamic
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "jamie" <[email protected]> wrote in message
>> news:[email protected]...
>> >i have an advanced filter that i want to display unique values, but
>> >due
>> >to
>> > cells being added later on, i have the filter on more cells than
>> > contents at
>> > the moment, but its taking a blank cell as a unique value, which i
>> > dont want.
>> >
>> > can you add an excpetion so that the cell must contain data or not
>> > empty to
>> > then be filtered? i have looked on this website but found nothing -
>> > http://office.microsoft.com/en-us/as...001781033.aspx
>> >
>> > if not, is there another way to do this?
>> >
>> > thanks
>> >
>> > Jamie
>>
>>
>>

Roger

I entered '=projects' and '= projects' and for both it says 'reference is
not valid'?

the defined range is called 'projects' all lower case.

thanks

"Roger Govier" wrote:

> Hi Jamie
>
> Data>Filter>Advanced Filter>List Range enter
> = myrange
> (or whatever you have called it) rather than $A$1:$M$1000.
> NOTE
> You must put the = sign in front of the name range
>
> --
> Regards
>
> Roger Govier
>
>
> "jamie" <[email protected]> wrote in message
> news:[email protected]...
> > Roger
> >
> > thanks for the info. i have completed the steps to create this range.
> > how
> > do i pass the naed range through the advanced filter? looking at the
> > wizzard, i am unsure where to tell it to look at the dynamic range.
> >
> > thanks
> >
> > "Roger Govier" wrote:
> >
> >> Hi Jamie
> >>
> >> Instead of defining a larger range with blanks to allow for later
> >> data
> >> entry, create a Dynamic named range for your data and pass the named
> >> range to Advanced Filter instead of your static range. That way,
> >> there
> >> will always be data in the relevant fields and you won't have the
> >> problems with blanks.
> >>
> >> For help on setting up Dynamic Ranges take a look at Debra
> >> Dalgleish's
> >> site
> >> http://www.contextures.com/xlNames01.html#Dynamic
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "jamie" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >i have an advanced filter that i want to display unique values, but
> >> >due
> >> >to
> >> > cells being added later on, i have the filter on more cells than
> >> > contents at
> >> > the moment, but its taking a blank cell as a unique value, which i
> >> > dont want.
> >> >
> >> > can you add an excpetion so that the cell must contain data or not
> >> > empty to
> >> > then be filtered? i have looked on this website but found nothing -
> >> > http://office.microsoft.com/en-us/as...001781033.aspx
> >> >
> >> > if not, is there another way to do this?
> >> >
> >> > thanks
> >> >
> >> > Jamie
> >>
> >>
> >>
>
>
>

Hi Jamie

Take another look at your defined name range to make sure it is correct.
Also, did you include the single quotes, ' or were the just there to
describe what you entered.
You don't need quotes around the actual entry.

--
Regards

Roger Govier


"jamie" <[email protected]> wrote in message
news:[email protected]...
> Roger
>
> I entered '=projects' and '= projects' and for both it says 'reference
> is
> not valid'?
>
> the defined range is called 'projects' all lower case.
>
> thanks
>
> "Roger Govier" wrote:
>
>> Hi Jamie
>>
>> Data>Filter>Advanced Filter>List Range enter
>> = myrange
>> (or whatever you have called it) rather than $A$1:$M$1000.
>> NOTE
>> You must put the = sign in front of the name range
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "jamie" <[email protected]> wrote in message
>> news:[email protected]...
>> > Roger
>> >
>> > thanks for the info. i have completed the steps to create this
>> > range.
>> > how
>> > do i pass the naed range through the advanced filter? looking at
>> > the
>> > wizzard, i am unsure where to tell it to look at the dynamic range.
>> >
>> > thanks
>> >
>> > "Roger Govier" wrote:
>> >
>> >> Hi Jamie
>> >>
>> >> Instead of defining a larger range with blanks to allow for later
>> >> data
>> >> entry, create a Dynamic named range for your data and pass the
>> >> named
>> >> range to Advanced Filter instead of your static range. That way,
>> >> there
>> >> will always be data in the relevant fields and you won't have the
>> >> problems with blanks.
>> >>
>> >> For help on setting up Dynamic Ranges take a look at Debra
>> >> Dalgleish's
>> >> site
>> >> http://www.contextures.com/xlNames01.html#Dynamic
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Roger Govier
>> >>
>> >>
>> >> "jamie" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >i have an advanced filter that i want to display unique values,
>> >> >but
>> >> >due
>> >> >to
>> >> > cells being added later on, i have the filter on more cells than
>> >> > contents at
>> >> > the moment, but its taking a blank cell as a unique value, which
>> >> > i
>> >> > dont want.
>> >> >
>> >> > can you add an excpetion so that the cell must contain data or
>> >> > not
>> >> > empty to
>> >> > then be filtered? i have looked on this website but found
>> >> > nothing -
>> >> > http://office.microsoft.com/en-us/as...001781033.aspx
>> >> >
>> >> > if not, is there another way to do this?
>> >> >
>> >> > thanks
>> >> >
>> >> > Jamie
>> >>
>> >>
>> >>
>>
>>
>>

Roger

The defined name and what i entered in the criteria for the filter are
idential, yes the '' were just to describe what i entered.

would i be able to email you an example of it?

thanks

Jamie

"Roger Govier" wrote:

> Hi Jamie
>
> Take another look at your defined name range to make sure it is correct.
> Also, did you include the single quotes, ' or were the just there to
> describe what you entered.
> You don't need quotes around the actual entry.
>
> --
> Regards
>
> Roger Govier
>
>
> "jamie" <[email protected]> wrote in message
> news:[email protected]...
> > Roger
> >
> > I entered '=projects' and '= projects' and for both it says 'reference
> > is
> > not valid'?
> >
> > the defined range is called 'projects' all lower case.
> >
> > thanks
> >
> > "Roger Govier" wrote:
> >
> >> Hi Jamie
> >>
> >> Data>Filter>Advanced Filter>List Range enter
> >> = myrange
> >> (or whatever you have called it) rather than $A$1:$M$1000.
> >> NOTE
> >> You must put the = sign in front of the name range
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "jamie" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Roger
> >> >
> >> > thanks for the info. i have completed the steps to create this
> >> > range.
> >> > how
> >> > do i pass the naed range through the advanced filter? looking at
> >> > the
> >> > wizzard, i am unsure where to tell it to look at the dynamic range.
> >> >
> >> > thanks
> >> >
> >> > "Roger Govier" wrote:
> >> >
> >> >> Hi Jamie
> >> >>
> >> >> Instead of defining a larger range with blanks to allow for later
> >> >> data
> >> >> entry, create a Dynamic named range for your data and pass the
> >> >> named
> >> >> range to Advanced Filter instead of your static range. That way,
> >> >> there
> >> >> will always be data in the relevant fields and you won't have the
> >> >> problems with blanks.
> >> >>
> >> >> For help on setting up Dynamic Ranges take a look at Debra
> >> >> Dalgleish's
> >> >> site
> >> >> http://www.contextures.com/xlNames01.html#Dynamic
> >> >>
> >> >> --
> >> >> Regards
> >> >>
> >> >> Roger Govier
> >> >>
> >> >>
> >> >> "jamie" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >i have an advanced filter that i want to display unique values,
> >> >> >but
> >> >> >due
> >> >> >to
> >> >> > cells being added later on, i have the filter on more cells than
> >> >> > contents at
> >> >> > the moment, but its taking a blank cell as a unique value, which
> >> >> > i
> >> >> > dont want.
> >> >> >
> >> >> > can you add an excpetion so that the cell must contain data or
> >> >> > not
> >> >> > empty to
> >> >> > then be filtered? i have looked on this website but found
> >> >> > nothing -
> >> >> > http://office.microsoft.com/en-us/as...001781033.aspx
> >> >> >
> >> >> > if not, is there another way to do this?
> >> >> >
> >> >> > thanks
> >> >> >
> >> >> > Jamie
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Sue Jamie

Just omit the NOSAPM from my address to mail direct

--
Regards

Roger Govier


"jamie" <[email protected]> wrote in message
news:[email protected]...
> Roger
>
> The defined name and what i entered in the criteria for the filter are
> idential, yes the '' were just to describe what i entered.
>
> would i be able to email you an example of it?
>
> thanks
>
> Jamie
>
> "Roger Govier" wrote:
>
>> Hi Jamie
>>
>> Take another look at your defined name range to make sure it is
>> correct.
>> Also, did you include the single quotes, ' or were the just there to
>> describe what you entered.
>> You don't need quotes around the actual entry.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "jamie" <[email protected]> wrote in message
>> news:[email protected]...
>> > Roger
>> >
>> > I entered '=projects' and '= projects' and for both it says
>> > 'reference
>> > is
>> > not valid'?
>> >
>> > the defined range is called 'projects' all lower case.
>> >
>> > thanks
>> >
>> > "Roger Govier" wrote:
>> >
>> >> Hi Jamie
>> >>
>> >> Data>Filter>Advanced Filter>List Range enter
>> >> = myrange
>> >> (or whatever you have called it) rather than $A$1:$M$1000.
>> >> NOTE
>> >> You must put the = sign in front of the name range
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Roger Govier
>> >>
>> >>
>> >> "jamie" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Roger
>> >> >
>> >> > thanks for the info. i have completed the steps to create this
>> >> > range.
>> >> > how
>> >> > do i pass the naed range through the advanced filter? looking
>> >> > at
>> >> > the
>> >> > wizzard, i am unsure where to tell it to look at the dynamic
>> >> > range.
>> >> >
>> >> > thanks
>> >> >
>> >> > "Roger Govier" wrote:
>> >> >
>> >> >> Hi Jamie
>> >> >>
>> >> >> Instead of defining a larger range with blanks to allow for
>> >> >> later
>> >> >> data
>> >> >> entry, create a Dynamic named range for your data and pass the
>> >> >> named
>> >> >> range to Advanced Filter instead of your static range. That
>> >> >> way,
>> >> >> there
>> >> >> will always be data in the relevant fields and you won't have
>> >> >> the
>> >> >> problems with blanks.
>> >> >>
>> >> >> For help on setting up Dynamic Ranges take a look at Debra
>> >> >> Dalgleish's
>> >> >> site
>> >> >> http://www.contextures.com/xlNames01.html#Dynamic
>> >> >>
>> >> >> --
>> >> >> Regards
>> >> >>
>> >> >> Roger Govier
>> >> >>
>> >> >>
>> >> >> "jamie" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> >i have an advanced filter that i want to display unique
>> >> >> >values,
>> >> >> >but
>> >> >> >due
>> >> >> >to
>> >> >> > cells being added later on, i have the filter on more cells
>> >> >> > than
>> >> >> > contents at
>> >> >> > the moment, but its taking a blank cell as a unique value,
>> >> >> > which
>> >> >> > i
>> >> >> > dont want.
>> >> >> >
>> >> >> > can you add an excpetion so that the cell must contain data
>> >> >> > or
>> >> >> > not
>> >> >> > empty to
>> >> >> > then be filtered? i have looked on this website but found
>> >> >> > nothing -
>> >> >> > http://office.microsoft.com/en-us/as...001781033.aspx
>> >> >> >
>> >> >> > if not, is there another way to do this?
>> >> >> >
>> >> >> > thanks
>> >> >> >
>> >> >> > Jamie
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>