Looking for some help, where I work the old system for booking out items from
our store involved a pen and paper, ive now computerised this.

I now have the staff booking out items, as I go I have been changing this to
make it more user friendly and this has included using Vlookup so the staff
only have to find the item number and the Vlookup completes the item
description.

=VLOOKUP(E2,$B$3:C749,2,FALSE)

Ive now come to a brick wall, basically 99% of our stocked items are given
unique stock numbers however we carry Consumable items (ZONNS), we have
approx 30 different items all under ZCONNS.

I have been surfing and reading articles but I cannot find the right formula
that I need, what I would like to do is for a staff member to type in the
item number ZCONNS and for it to then bring up a drop down list or similar
for us to choose the correct item to book out of store.

Below is a sample list of some of the consumables:

ZCONNS Bootlace Ferrule Violet 0.25mm
ZCONNS Bootlace Ferrule Pink 0.34mm
ZCONNS Bootlace Ferrule White 0.5mm
ZCONNS RJ11's - 4 contacts - STR
ZCONNS RJ11's - 4 contacts - Solid
ZCONNS RJ12's - 6 contacts - STR
ZCONNS RJ12's - 6 contacts - Solid
ZCONNS RJ45's - Stranded - STR
ZCONNS RJ45's - Solid
ZCONNS RJ45's - Shielded Stranded - STR
ZCONNS RJ45's - Shielded Solid
ZCONNS Resistors etc - All Sorts
ZCONNS Cable Ties pkt100x(100x2.5) Natural
ZCONNS Cable Ties pkt100x(100x2.5) Black

Any help is greatly appreciated.

Regards David

Vlookup is the wrong function for you, it returns only the first value that
it finds matching the one you lookup however many columns you specify, as you
know. To make a drop down list you can do a data validation:

Date, validation, allow: list. Press the cell selection button that has
just appeared below the list option then choose the data that you want to be
allowed (each cell chosen will represent a separate item on the list). Then
you can set whether your staff are allowed to input other values or not and
what it says when they put in the wrong value etc. If you want to do a
vlookup function for this, you will need to use a unique value for each
product, as that is how vlookup works. At least, that's what I know!

"Dudeone" wrote:

Looking for some help, where I work the old system for booking out items from
our store involved a pen and paper, ive now computerised this.

I now have the staff booking out items, as I go I have been changing this to
make it more user friendly and this has included using Vlookup so the staff
only have to find the item number and the Vlookup completes the item
description.

=VLOOKUP(E2,$B$3:C749,2,FALSE)

Ive now come to a brick wall, basically 99% of our stocked items are given
unique stock numbers however we carry Consumable items (ZONNS), we have
approx 30 different items all under ZCONNS.

I have been surfing and reading articles but I cannot find the right formula
that I need, what I would like to do is for a staff member to type in the
item number ZCONNS and for it to then bring up a drop down list or similar
for us to choose the correct item to book out of store.

Below is a sample list of some of the consumables:

ZCONNS Bootlace Ferrule Violet 0.25mm
ZCONNS Bootlace Ferrule Pink 0.34mm
ZCONNS Bootlace Ferrule White 0.5mm
ZCONNS RJ11's - 4 contacts - STR
ZCONNS RJ11's - 4 contacts - Solid
ZCONNS RJ12's - 6 contacts - STR
ZCONNS RJ12's - 6 contacts - Solid
ZCONNS RJ45's - Stranded - STR
ZCONNS RJ45's - Solid
ZCONNS RJ45's - Shielded Stranded - STR
ZCONNS RJ45's - Shielded Solid
ZCONNS Resistors etc - All Sorts
ZCONNS Cable Ties pkt100x(100x2.5) Natural
ZCONNS Cable Ties pkt100x(100x2.5) Black

Any help is greatly appreciated.

Regards David

Thanks for the reply, yes your right regarding the Vlookup, what I think I
need to do is use an IF statement but although ive used excel a lot its not
been writing formulas.

So I still need the Vlookup as I want to be able to find the item
description and for it to populate a set row with said description.

But in the formula I need it to be able to recognise ZCONNS and give me a
list of all associated items and then allow the user to choose the correct
item.

Here is a further sample of our item numbers:

ZVIDCAB001 1694A €“ Black
ZVIDCAB002 1694A €“ Blue
ZVIDCAB003 1694A €“ Red
ZVIDCAB004 1694A €“ Breen
ZCONNS Bootlace Ferrule Violet 0.25mm
ZCONNS Bootlace Ferrule Pink 0.34mm
ZCONNS Bootlace Ferrule White 0.5mm
ZCONNS RJ11's - 4 contacts - STR
ZCONNS RJ11's - 4 contacts - Solid

What I think I need is for the statement to read something like this:

If E3 = ZCONNS then display list, Else ,VLOOKUP(E2,$B$3:C749,2,FALSE)

However putting this in Excel is not as easy.


"exoticdisease" wrote:

Vlookup is the wrong function for you, it returns only the first value that
it finds matching the one you lookup however many columns you specify, as you
know. To make a drop down list you can do a data validation:

Date, validation, allow: list. Press the cell selection button that has
just appeared below the list option then choose the data that you want to be
allowed (each cell chosen will represent a separate item on the list). Then
you can set whether your staff are allowed to input other values or not and
what it says when they put in the wrong value etc. If you want to do a
vlookup function for this, you will need to use a unique value for each
product, as that is how vlookup works. At least, that's what I know!

"Dudeone" wrote:

Looking for some help, where I work the old system for booking out items from
our store involved a pen and paper, ive now computerised this.

I now have the staff booking out items, as I go I have been changing this to
make it more user friendly and this has included using Vlookup so the staff
only have to find the item number and the Vlookup completes the item
description.

=VLOOKUP(E2,$B$3:C749,2,FALSE)

Ive now come to a brick wall, basically 99% of our stocked items are given
unique stock numbers however we carry Consumable items (ZONNS), we have
approx 30 different items all under ZCONNS.

I have been surfing and reading articles but I cannot find the right formula
that I need, what I would like to do is for a staff member to type in the
item number ZCONNS and for it to then bring up a drop down list or similar
for us to choose the correct item to book out of store.

Below is a sample list of some of the consumables:

ZCONNS Bootlace Ferrule Violet 0.25mm
ZCONNS Bootlace Ferrule Pink 0.34mm
ZCONNS Bootlace Ferrule White 0.5mm
ZCONNS RJ11's - 4 contacts - STR
ZCONNS RJ11's - 4 contacts - Solid
ZCONNS RJ12's - 6 contacts - STR
ZCONNS RJ12's - 6 contacts - Solid
ZCONNS RJ45's - Stranded - STR
ZCONNS RJ45's - Solid
ZCONNS RJ45's - Shielded Stranded - STR
ZCONNS RJ45's - Shielded Solid
ZCONNS Resistors etc - All Sorts
ZCONNS Cable Ties pkt100x(100x2.5) Natural
ZCONNS Cable Ties pkt100x(100x2.5) Black

Any help is greatly appreciated.

Regards David

Have a look at Debra Dalgleish's site he

http://www.contextures.com/xlDataVal02.html

where she shows how to set up dependent data validation. Basically you
will have a list of items like ZCONNS, ZVIDCAB001 etc which you can
choose from the first drop-down. Then by naming the ranges which these
items link to, a second drop-down can automatically list the sub-
divisions. If you have further data associated with, say, Bootlace
Ferrule Violet 0.25mm, then you can use a VLOOKUP on this column.

Hope this helps.

Pete

On Jul 28, 9:53*pm, Dudeone wrote:
Thanks for the reply, yes your right regarding the Vlookup, what I think I
need to do is use an IF statement but although ive used excel a lot it’s not
been writing formulas.

So I still need the Vlookup as I want to be able to find the item
description and for it to populate a set row with said description.

But in the formula I need it to be able to recognise ZCONNS and give me a
list of all associated items and then allow the user to choose the correct
item.

Here is a further sample of our item numbers:

ZVIDCAB001 * * *1694A – Black
ZVIDCAB002 * * *1694A – Blue
ZVIDCAB003 * * *1694A – Red
ZVIDCAB004 * * *1694A – Breen
ZCONNS *Bootlace Ferrule Violet 0.25mm
ZCONNS *Bootlace Ferrule Pink 0.34mm
ZCONNS *Bootlace Ferrule White 0.5mm
ZCONNS *RJ11's - 4 contacts - STR
ZCONNS *RJ11's - 4 contacts - Solid

What I think I need is for the statement to read something like this:

If E3 = ZCONNS then display list, Else ,VLOOKUP(E2,$B$3:C749,2,FALSE)

However putting this in Excel is not as easy.



"exoticdisease" wrote:
Vlookup is the wrong function for you, it returns only the first value that
it finds matching the one you lookup however many columns you specify, as you
know. *To make a drop down list you can do a data validation:

Date, validation, allow: list. *Press the cell selection button that has
just appeared below the list option then choose the data that you want to be
allowed (each cell chosen will represent a separate item on the list). *Then
you can set whether your staff are allowed to input other values or not and
what it says when they put in the wrong value etc. *If you want to do a
vlookup function for this, you will need to use a unique value for each
product, as that is how vlookup works. *At least, that's what I know!

"Dudeone" wrote:

Looking for some help, where I work the old system for booking out items from
our store involved a pen and paper, ive now computerised this.

I now have the staff booking out items, as I go I have been changing this to
make it more user friendly and this has included using Vlookup so the staff
only have to find the item number and the Vlookup completes the item
description.

=VLOOKUP(E2,$B$3:C749,2,FALSE)

Ive now come to a brick wall, basically 99% of our stocked items are given
unique stock numbers however we carry Consumable items (ZONNS), we have
approx 30 different items all under ZCONNS.

I have been surfing and reading articles but I cannot find the right formula
that I need, what I would like to do is for a staff member to type in the
item number ZCONNS and for it to then bring up a drop down list or similar
for us to choose the correct item to book out of store.

Below is a sample list of some of the consumables:

ZCONNS * * * *Bootlace Ferrule Violet 0.25mm
ZCONNS * * * *Bootlace Ferrule Pink 0.34mm
ZCONNS * * * *Bootlace Ferrule White 0.5mm
ZCONNS * * * *RJ11's - 4 contacts - STR
ZCONNS * * * *RJ11's - 4 contacts - Solid
ZCONNS * * * *RJ12's - 6 contacts - STR
ZCONNS * * * *RJ12's - 6 contacts - Solid
ZCONNS * * * *RJ45's - Stranded - STR
ZCONNS * * * *RJ45's - Solid
ZCONNS * * * *RJ45's - Shielded Stranded - STR
ZCONNS * * * *RJ45's - *Shielded Solid *
ZCONNS * * * *Resistors etc - All Sorts
ZCONNS * * * *Cable Ties pkt100x(100x2.5) Natural
ZCONNS * * * *Cable Ties pkt100x(100x2.5) Black

Any help is greatly appreciated.

Regards David- Hide quoted text -

- Show quoted text -