exceptions in filtering

[Guest]

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/assistance/HP052001781033.aspx

if not, is there another way to do this?

thanks

Jamie

 

[Roger Govier]

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

 

[Guest]

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]

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

 

[Guest]

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]

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.

 

[Guest]

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]

Sue Jamie

Just omit the NOSAPM from my address to mail direct