seaching a Sheet

Why not use the (Custom) option of the dropdown?

i gave that a go and the drop down is not fully populated by all 7000 unique Product codes, there seems to be around 3 or 4 hundred only.

even typing in a custom one does not allow me to get at the other product codes.

Thanks for your Reply

RaelRigs

What do you mean?

my excel sheet is a sheet for ordering parts from traders,

in the sheet there are 4 columns Manifacturer, part number, trade price and retail price.

the sheet is 7000 items long.

auto filter does not populate its dropdown menu with all 7000 items, only roughly the first 400 items are listed.

i need a way to be able to type in to a cell the part number i need to find a price for, so the only item showing in the list are the items relating to that part number. ( like auto filter does )

i have thought about an access database but i really have no idea what im doing with that. ( Access novice ) so far in access i have imported the data from the excel sheet but can get no further. i like the idea of the ms access data access page that it can make but so far i have not found a way to make the part code seachable from the data access page.

as you can appriciate scrolling through that many records can become confusing and hard to find what your looking for.

thanks for your help so far,

RaelRigs

I understand your setup but I don't see why you can't use (Custom).

because its not finding the data near the bottom of the sheet, from 400 onwards.

Hi,
Assuming your data is in columns A, B, C and D. Make some room above your lists, Minimum 2 rows.

Copy Your field headings to A1 to D1. Leaving at least one row between these headings and your lists.

Highlight all 7,000 records including the field names then

Data; Filter; Advanced Filter; Ensure the "list range" = your list area,
Set the "criteria range" to $A$1 to $D$2

Click ok

Key your part number into A2 (assuming your part numbers are in column A)

Now Data:Filter:Advanced: Click ok and the list will filter out all records except the part no. you keyed into A1

To show all records again Data: Filter: Show All

To show a different record key in another part number and
Data:Filter:Advanced: Click ok

You can show several part numbers at once by extending your criteria range and keying part no. in column A i.e. A1, A2, A3,

You can key the supplier into the supllier column and get a list of all parts from that supplier

Hope this helps










You might want to look at the help in excel for Advanced filter

is there any way to automate the filtering process, I,E type in part number the click go button?
im trying to make this process and dummy proof as posible.
for instance some kind of scripting or recorded macro to allow me to insert a button and have that do the actions you discribed above

Thanks for your help, it does indeed work like that.

Raelrigs

Yes there is.

You can record the the whole process and create a macro. But I would set the Data Filter up and then record the macro that can be activated AFTER the part no. has been entered i.e. the last bit.

Key a part no. into A2
start the macro recorder.
assign a shotcut key i.e "f" for filter
do the Data/filter/advanced filter/ok
stop the recorder.

To run the macro press Ctrl and "f" or create your own button and assign the macro to it. Put it on your toolbar and click the button when you want to run the macro.

:wink: