Use Data Filter on Hidden Data? (E2003 SP3)

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Use Data Filter on Hidden Data? (E2003 SP3)

Post by steveh »

Good afternoon

I have 1700 rows in 4 columns of Data on which I use an autofilter, I would however like to have all of the source data hidden until the user selects what they want to view. depending on what column of data they choose they will see- Columns A, B and C only return a unique result, but if column D is selcted there could be anything from 1 - 100 results.

I thought that by moving the data to the bottom of the page (A60000:D61690) I could use the auto filter (I have never used it before so it was a shot in the dark) to nominate where to look for the data but it did not even give me the option. Can I do this or should I look for other methods?
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Use Data Filter on Hidden Data? (E2003 SP3)

Post by HansV »

I wouldn't move the data to the bottom of the sheet, that's very inefficient.
You could select Custom... from one of the filter dropdown arrows, and specify a non-existent value. When you apply the filter, all data rows will be hidden.
Best wishes,
Hans

User avatar
Don Wells
5StarLounger
Posts: 689
Joined: 27 Jan 2010, 16:45
Location: Ottawa, Ontario, Canada

Re: Use Data Filter on Hidden Data? (E2003 SP3)

Post by Don Wells »

HansV wrote:...and specify a non-existent value. ...
Didn't you mean "..and specify Blanks. ..."?
Regards
Don

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Use Data Filter on Hidden Data? (E2003 SP3)

Post by HansV »

No - the data could contain blanks in some columns but not in others, so selecting Blanks might not hide all data rows.
Best wishes,
Hans

User avatar
Don Wells
5StarLounger
Posts: 689
Joined: 27 Jan 2010, 16:45
Location: Ottawa, Ontario, Canada

Re: Use Data Filter on Hidden Data? (E2003 SP3)

Post by Don Wells »

Of course! I forgot about the "Custom" entries.

Thank you Hans.
Regards
Don

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: Use Data Filter on Hidden Data? (E2003 SP3)

Post by steveh »

HansV wrote:I wouldn't move the data to the bottom of the sheet, that's very inefficient.
You could select Custom... from one of the filter dropdown arrows, and specify a non-existent value. When you apply the filter, all data rows will be hidden.
Thanks Hans

Perfect as usual
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin