Help needed with lookup and filtering

[Tony Georgiou]

Hi, I have a purchasing system in excel. It simply works like this, the
order sheet looksup prices, units and descriptions from a price list on
another sheet using a vlookup command. My probelm is when it comes to
entering the part number which is needed to be looked up, the user does not
always know the part number. Example part numbers are: P94005-26-A,
P94005-27-A, P94005-27-B etc.
So..how can i make the part number entry field in the order sheet lookup and
autofilter as you type to narrow the possible part numbers that are in the
lookup table??

Can it be done? I know the user can just do a search on the pricelist
worksheet, but this slows things down.

Any help greatly appreciated.

Regards,


Tony

 

[Anon]

Hi, I have a purchasing system in excel. It simply works like this, the
order sheet looksup prices, units and descriptions from a price list on
another sheet using a vlookup command. My probelm is when it comes to
entering the part number which is needed to be looked up, the user does not
always know the part number. Example part numbers are: P94005-26-A,
P94005-27-A, P94005-27-B etc.
So..how can i make the part number entry field in the order sheet lookup and
autofilter as you type to narrow the possible part numbers that are in the
lookup table??

Can it be done? I know the user can just do a search on the pricelist
worksheet, but this slows things down.

I don't know that you can do exactly as you ask. However, in a similar
situation I have used data validation to achieve the same result. Basically,
you apply validation to the cells where the user has to enter the part
number. The validation list is your list of part numbers. Then, instead of
typing it in, your user can simply select the required part number from the
drop-down box.

Here is a simple example of how to do this.
Suppose your list of part numbers is in Sheet2!A1:A10. Select this range and
give it a name, say "partnumbers". (You have to use a name, as otherwise
validation will not work to a different worksheet.)
Now select the cell (or cells, or complete column) on Sheet1 where the user
needs to enter a part number.
Data > Validation
In "Allow:" box, choose "List".
In "Source" box type:
=partnumbers
Make sure that "In-cell dropdown" is checked and click OK.
Now whenever one of these cells is selected, a drop-down arrow will appear,
allowing the user to choose from the available part numbers.

As well as making it easier for the user, this ensures that only valid part
numbers can be entered.