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" wrote in message
...
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" wrote in message
...
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

DataFilterAdvanced FilterList 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" wrote in message
...
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" wrote in message
...
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

DataFilterAdvanced FilterList 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

DataFilterAdvanced FilterList 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

DataFilterAdvanced FilterList 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

DataFilterAdvanced FilterList 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" wrote in message
...
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" wrote in message
...
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