I hope anyone can help......I'm reaching the end of my worksheet and can't
figure out how to do this?
I have an order to run at work and it consists of labels inserts and
cartons......but some orders have a few more components to them.....so is
there a way for the user to select from a drop down box for them to choose a
component and when they do, it will contain the right calculations to show if
they have enough and let them select from another box until they got all
components on the sheet.
--
Mr.B

Hi,

This is pretty vague. What does your data look like, where do you want drop
down lists and what should the drop down lists have in them?

The answer is probably yes. Have a VLOOKUP function refernce the drop down
list.

Cheers,
Shane Devenshire

"Mr.B" wrote:

I hope anyone can help......I'm reaching the end of my worksheet and can't
figure out how to do this?
I have an order to run at work and it consists of labels inserts and
cartons......but some orders have a few more components to them.....so is
there a way for the user to select from a drop down box for them to choose a
component and when they do, it will contain the right calculations to show if
they have enough and let them select from another box until they got all
components on the sheet.
--
Mr.B

Well I have labels, Inserts, Cartons, and boxes. These components go into
every order and the spreadsheet calculates how much extra material we have
per component, now, some orders have other components lets say a syringe I
would like to have a box to choose from a list of things like the syringe.
What I would like to have happen is when a component from the box is chosen
another box will appear so you can choose another one and so on until you
leave one of the boxes blank up to 4 boxes???
sounds a little complicated to me but that would be nice...

--
Mr.B


"Shane Devenshire" wrote:

Hi,

This is pretty vague. What does your data look like, where do you want drop
down lists and what should the drop down lists have in them?

The answer is probably yes. Have a VLOOKUP function refernce the drop down
list.

Cheers,
Shane Devenshire

"Mr.B" wrote:

I hope anyone can help......I'm reaching the end of my worksheet and can't
figure out how to do this?
I have an order to run at work and it consists of labels inserts and
cartons......but some orders have a few more components to them.....so is
there a way for the user to select from a drop down box for them to choose a
component and when they do, it will contain the right calculations to show if
they have enough and let them select from another box until they got all
components on the sheet.
--
Mr.B

Hi,

Suppose your first list will be in column D and the other lists in columns E:H

1 First create lists for each of the drop downs you want. For now just put
them where they are easy for you to text with.
2. In each of the column D:H create a data validation using your lists:

Here are the steps for creating a data validation list:

1. Select the range
2. Choose Data, Validation
3. From the Allow drop-down and choose List
4. In the Source box enter =M1:M10 or whatever range you choose to put the
list into.
5. For the data validations for columns E:H turn the In cell drop-down check
box off. Leave it on in column D.

A. Lists can be manually entered into the Source box: 1,2,Monday,Tuesday
(no quotes are necessary, but the entries should be separated by commas.)
B. Lists can get their data from ranges: =$A$1:$A$10 (which is generally a
better choice)

If you use a range reference, as above, the range must be on the same sheet as the data validation.
If you want to refer to a range on another sheet that range must be named and you references it as =MyListName

You can name a range by selecting it and typing the name into the Name Box
(top left side of the Formula Bar) and pressing Enter or you can use the
Insert, Name, Define command.

Now we need to add code to activate the other drop-down lists:

1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Isect As Range
Set Isect = Application.Intersect(Target, [D1:H1])
If Not Isect Is Nothing Then
Target.Offset(0, 1).Validation.InCellDropdown = True
Target.Offset(0, 1).Select
End If
End Sub

This is designed specifically for D1:H1, since the dropdown lists are
probably going to be on many rows your range above would reflect that.

Now when the user pick an item from the drop down list in column D the
cursor is moved to column E and the drop down arrow displayed. Similarly as
they move to the right.

To pull back information about the users pick you can put a formula in
another column, such as C, for example, using our setup C1. That formula
would be
=VLOOKUP(D1,$M$1:$P$20,3,FALSE)

The range M1:P20 would contain a reference table with information about the
product they picked. For example, suppose column 3 contained the "number on
hand". The above formula would return that info. The table might looks
something like:

Item Cost On Hand Other Info
Boxes 2 2
Syringes 10 7
Tapes 11 18
Bandages 17 12
Sterile Gauze 19 10

If this helps, please click the Yes button.

cheers,
Shane Devenshire

"Mr.B" wrote:

Well I have labels, Inserts, Cartons, and boxes. These components go into
every order and the spreadsheet calculates how much extra material we have
per component, now, some orders have other components lets say a syringe I
would like to have a box to choose from a list of things like the syringe.
What I would like to have happen is when a component from the box is chosen
another box will appear so you can choose another one and so on until you
leave one of the boxes blank up to 4 boxes???
sounds a little complicated to me but that would be nice...

--
Mr.B


"Shane Devenshire" wrote:

Hi,

This is pretty vague. What does your data look like, where do you want drop
down lists and what should the drop down lists have in them?

The answer is probably yes. Have a VLOOKUP function refernce the drop down
list.

Cheers,
Shane Devenshire

"Mr.B" wrote:

I hope anyone can help......I'm reaching the end of my worksheet and can't
figure out how to do this?
I have an order to run at work and it consists of labels inserts and
cartons......but some orders have a few more components to them.....so is
there a way for the user to select from a drop down box for them to choose a
component and when they do, it will contain the right calculations to show if
they have enough and let them select from another box until they got all
components on the sheet.
--
Mr.B