Printing or exporting or copying the data filter drop-down boxes

Peter Jordan

unread,

Feb 24, 2003, 3:05:04 PM2/24/03

to

When I go data->filter->auto filter, then click on one of
the down-arrows, I see a list of data types in that
column. How can I export and/or print and/or copy this
list? (This would be invaluable to me in auditing a
spreadsheet to make sure I'm sumif-ing for all the
categories).

Thanks,

Peter Jordan
peter...@mindspring.com
(sure would appreciate an email response)

Gord Dibben

unread,

Feb 24, 2003, 4:22:37 PM2/24/03

to

Peter

Select that column. Data>Filter>Advanced Filter. Check "Unique records only"
and "Copy to another location".

This will give you the same list as would see in the Autofilter drop-down.

Gord Dibben Excel MVP - XL97 SR2 & XL2002

Peter Jordan

unread,

Feb 24, 2003, 6:05:46 PM2/24/03

to

It tells me Microsoft Excel cannot determine which row in
your selection contains column labels. . . . (Why the
*hell* won't Microsoft let me select and paste the error
message text?)

(I don't know how to officially create labels -- I simply
use the top 1st or 2nd or 3rd row in the spreadsheet to
put my labels in).

>.
>

Gord Dibben

unread,

Feb 24, 2003, 6:45:22 PM2/24/03

to

Peter

If in Row 1 you have a title such as "Widgets", Excel will recognize this as a
"label".

If you have none, just hit OK and Excel will use Top cell in Row 1.

Gord

On Mon, 24 Feb 2003 15:05:46 -0800, "Peter Jordan"

Peter Jordan

unread,

Feb 25, 2003, 12:24:27 PM2/25/03

to

1. I select the column (column G in this case).
2. Data->filter->advanced filter
3. The advanced filter box appears.
4. List range has $A$1:$G$514 in it
Criteria range is blank
5. I check Copy to another location and Unique records
only.
6. I click OK
7. Error message: ! Reference is not valid.

(If I open a new spreadsheet labeled Book2 and tell it to
copy to Book2, I get the error message, the text you
entered is not a valid reference or defined name).

>.
>

Peter Jordan

unread,

Feb 25, 2003, 12:47:18 PM2/25/03

to

I had some blank cells in the column, and I was not
specifying a range. Now I seem to be getting some results!

(I'm still a little bewildered by just what I'm doing, but
I think I'm heading in the right direction now).

Thanks,

Peter

>.
>

Gord Dibben

unread,

Feb 25, 2003, 2:23:17 PM2/25/03

to

Peter

It looks to me that you have omitted selecting a starting cell for the Copy To
location. I neglected to mention this in my original Post.

From Debra Dalgleish at

http://www.contextures.on.ca/xladvfilter01.html#FilterUR

Filter Unique Records
You can use an Advanced Filter to extract a list of unique items in the
database. For example, get a list of customers from an order list, or compile
a list of products sold:

Select a cell in the database.
From the Data menu, choose Filter, Advanced Filter.
Choose 'Copy to another location'.
For the List range, select the column(s) from which you want to extract the
unique values.
Leave the Criteria Range blank.
Select a starting cell for the Copy to location.
Add a check mark to the Unique records only box.
Click OK.

Gord

On Tue, 25 Feb 2003 09:24:27 -0800, "Peter Jordan"

Gord Dibben

unread,

Feb 25, 2003, 2:25:20 PM2/25/03

to

In addition to my post of two mintes ago....the list range should be
$G$1:$G$514

Gord

On Tue, 25 Feb 2003 09:24:27 -0800, "Peter Jordan"