cascading combo boxes...???

Gurps

New Member
Joined
Feb 16, 2002
Messages
4
is there any way in which I can get the selection from one combo box to influence the second?? In other words, The second combo box is dependant on the first.
I am using Excel XP.

any help would be appreciated.
cheers
 
hey aladin y indirect doesnt work for no adjacent range or cell?y do we ve to write in same column or rows name like u gave example of USA and France?y indirect doesnt work>?

There is no reason INDIRECT not to work, except with dynamic named ranges. Would you post a small sample and tell us which lists are dependent?
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Is there any way to have a value attached to selections in the first and then dependant selections?

For example, assume these are selections from a list

A1: Package1 A2: all supplied A3 (I want this to be the price)

So ideally i would like package1 to be $190, and all supplied to be -$70, so the total in A3 to be $120.

the list in A1 has about 15 selections, and the list in A2 has about 10 selection (as conditions to A1), so hoping there is a simple way to calculate the total other than typing in every possible total costs.

Thanks in advance
 
Upvote 0
Is there any way to have a value attached to selections in the first and then dependant selections?

For example, assume these are selections from a list

A1: Package1 A2: all supplied A3 (I want this to be the price)

So ideally i would like package1 to be $190, and all supplied to be -$70, so the total in A3 to be $120.

the list in A1 has about 15 selections, and the list in A2 has about 10 selection (as conditions to A1), so hoping there is a simple way to calculate the total other than typing in every possible total costs.

Thanks in advance


Shouldn't that be just:

A3:

=SUM(A1:A2)

where A3 is a formula cell, not a data-validated cell.
 
Upvote 0
Shouldn't that be just:

A3:

=SUM(A1:A2)

where A3 is a formula cell, not a data-validated cell.

LOL, very good haha. Let me try to explain better :)

So in A1 and A2 are dropdown lists. A1 has selections "Package1" through to "Package8", and A2 has selections "addextra", "lessextra" and "allsupplied".

Package 1 in reality has a value of $190, and All Supplied in reality has a value of -$70, so if i supplied a customer with Package 1 all supplied, the total price would be $120.

Is there a way to Select Package1 in A1, but excel recognises this selection to imply $190, and select Allsupplied in A2 for excel to recognise this as -$70, to give a total in A3 as $120.

The 2 ways i can think of is 1- use the if function multiple times, very time consuming, or 2- have columns in between with conditional dropdown lists giving only the values applicable to each package, and then sum those columns as you suggested. I'm just hping there is an easier way.

Thanks
 
Upvote 0
LOL, very good haha. Let me try to explain better :)

So in A1 and A2 are dropdown lists. A1 has selections "Package1" through to "Package8", and A2 has selections "addextra", "lessextra" and "allsupplied".

Package 1 in reality has a value of $190, and All Supplied in reality has a value of -$70, so if i supplied a customer with Package 1 all supplied, the total price would be $120.

Is there a way to Select Package1 in A1, but excel recognises this selection to imply $190, and select Allsupplied in A2 for excel to recognise this as -$70, to give a total in A3 as $120.

The 2 ways i can think of is 1- use the if function multiple times, very time consuming, or 2- have columns in between with conditional dropdown lists giving only the values applicable to each package, and then sum those columns as you suggested. I'm just hping there is an easier way.

Thanks

Create a 2-column range on a sheet called Admin in A:B from row 2 downwards, which house

Package 1,190
Package 2.160
...
All Supplied,-70

Name the range in column A with Package 1, etc. CHOICES and
the range in column B DOLLARS.

Now back to the choices made thru data-validated cells A1 and A2 on your target sheet: In A3 enter:

=SUMPRODUCT(SUMIF(CHOICES,A1:A2,DOLLARS))
 
Upvote 0
Hello! This thread is great! Today I learned something new! Thanks Aladin for your contribution...
Regards from Monterrey Mexico
 
Upvote 0
I have a similar problem. I have 7 worksheets basically one for each supplier, each sheet has list of products, description, qty and cost. I have created a drop down for each supplier in B2 and cell C3 will return the product list for that supplier (indirect function). What i would like to do is also bring the cost across as well. The target columns in the worksheets are A? and D?
 
Upvote 0
I have a similar problem. I have 7 worksheets basically one for each supplier, each sheet has list of products, description, qty and cost. I have created a drop down for each supplier in B2 and cell C3 will return the product list for that supplier (indirect function). What i would like to do is also bring the cost across as well. The target columns in the worksheets are A? and D?

Care to post the list you have in B2?
 
Upvote 0
This is the list in B2 <TABLE style="WIDTH: 60pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=80><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=80>PS3list</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Wii</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>PC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>XBOX</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>DS</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Accessories</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Consoles</TD></TR></TBODY></TABLE>

These correspond to the sheet names in the work book, each sheet has has each sheet has 4 columns; A=Product Code, B=Description, C=Qty, D=Price. On another sheet, cell B2 brings up the list above, cell C2 has data validation as "List" and source as "=Indirect(B2) which gives the list of product codes for the selection in B2. What I would like to do is bring the cost over at the same time. Hope this makes sense.
 
Upvote 0
Just saw the request to have the dropdown shown.

I color all cells that have a dropdown (say light blue)

Then on that sheet I add the following code and when the cell is clicked, the drop down list shows.

Without the code, you click the cell, it shows an arrow, you then have to click the arrow to display the dropdown list

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    ' automatic DropDown when the cell is clicked, the drop values display
 
    Dim lngValidType As Long
    On Error Resume Next
    lngValidType = Target.Validation.Type
    If lngValidType = 0 Then Exit Sub
    If lngValidType = 3 Then SendKeys "%{down}"
 
End Sub

I do agree that this thread is getting too long....
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top