Excel AutoFilter problem

[Skittle]

Hi,

I have Access 97. The auto-filter either is not working or I am not using it correctly.

Example:-

I add a set of numbers into a brand new Excel file column.
One number per column.

123
567
129
567
128

I then highlight the entire column and from the menu bar select 'Data' followed by 'Filter' and then 'Autofilter'. This puts a litle down arrow on the first cell in my column with any data on it and I can click on it to select 'custom'. This then brings up a dialogue box asking for the filter details.

I select 'equals' in the left column and '123*' in the right column but nothing is selected. I tried changing 'equals' to 'begin with' but also get nothing.

Can anybody tell me where I'm going wrong?

 

[xlbo]

Are you entering 1 number per COLUMN or 1 number per ROW ???
This makes a BIG difference - filters in excel work VERTICALLY, unlike in Access where you can specify different criteria for each field but the filter will work on the whole table Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Wray69]

If you imported this data in from another application like a database it is possible that excel is seeing your numbers as text. To fix this you can place a 1 in a blank cell and do an edit copy on it, then select your range of #'s and do an Edit --> Paste Special --> Multiply, this will multiply your numbers by one changing the format back to numbers. Hope this helps you.

Regards,

Wray

 

[carrr]

Why are you asking it for '123*'???
Is the above listed data your only data so far?
Autofilter filters lines of data, therefore if you only have the above data in a row, and it's your only row, then if you select 'equals 123', you're going to see your data row...i.e. you have one row containing '123', there's nothing to filter out.

 

[Skittle]

I see how my turn of phrase caused some confusion here.
I meant all of the numbers are in one column, not a row.

 

[xlbo]

The reason you are getting no hits is that the CUSTOM function of an autofilter compares TEXT
You have the opposite problem to the one WRAY69 has detailed

If header in A1 and numbers starting in A2, then in B2 enter
=TEXT(A1,"0&quot
copy this down to the bottom of your data-set
Then, you have 2 options:
1) Filter on this new column (if you want to retain the properties of numbers in your original data)
2)Copy this new data and seloect A2
Then goto Edit>Pastespecial and select Values
Your "numbers" will now be text and the custom autofilter will work for "contains" and "begins with"

HTH Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur