Question

Locked

Need help making dependant drop down lists in excel!

By andrew.petry ·
Hello!
Let me start off by giving you an idea of what I've been working on. I've been making dependant drop down lists in excel from right to left using data validation. For example in the far left cell you click and select a manufacturer (ie Case Internation or Bobcat), then in the next drop down list you choose a model (ie 400-Series SSL or M-Series Loader/Backhoe) then the next drop down will give you the base prep time for that machine ( I have not figured out how to just pull a number and display it from the data sheet. I've just been using the drop-down because that's all i've been able to figure out. So when you pull down that drop down there is only one selection possible), then to the right of that is a series of drop down lists stacked on top of one another (about ten or so) where you can select various implements that are to go on that machine (ie Block Heater, Air Conditioning, Radio, etc) each with another drop down list to the right of them displaying additional prep time (again using drop downs to pull the values and display them). WOO! Anyway, sometimes when when selecting implements all of them won't list in the drop-down. For example if I choose Case International in the far left, then M-Series Loader/Backhoe in the model drop down, then go to select the implements and not all are shown. On my data sheet I have made sure that M-Series Loader/Backhoe is typed the same in every cell with each implement for that unit listed directly to the left. Something like this...

Model | Implement |PrepTim
M-Series Loader/Backhoe | Block Heater | 3
M-Series Loader/Backhoe | Radio | 6
M-Series Loader/Backhoe | Air Cond. | 20

you get the point. Do you think that the reason that all implements are not listed because all of the models are stacked on top of each other? Is there some sort of limit to the data you can have in a sheet? My sheet doesn't exceed 125 lines (so far) and it's still giving me issues. Is there a better way to make a "prep time calculator" in excel? Are there any helpful aspects to VBA in excel that would be beneficial? I've made a CommandButton1 that clears all of the selections after you have finished so you can start over. Other than that I haven't messed with VBA much. Could I utilize IF functions to help solve my problem? I've been using OFFSET and data validation to pull my data...is that what's causing my issue? PLEASE HELP! =)

THANKS sO MUCH!

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Answers

Collapse -

Is this the same spreadsheet from your previous post?

by ThumbsUp2 In reply to Need help making dependan ...

This one?

http://techrepublic.com.com/5208-6230-0.html?forumID=101&threadID=306982&messageID=3056672

It's always better to keep a conversation concerning the same topic all within the same thread. That way, anybody who volunteers to help can read what has already been tried. You can post your question as a response to your original question and, by simply posting, will pop your original thread back to the top of the forum queue.

Collapse -

Some VBA to try....

by susan.forister In reply to Need help making dependan ...

Hi Andrew,

Sounds like you know Excel really well, so I think you'll grasp this VBA I have below.

To try it out, first create a validation list in cell C4 that has M-Series Loader/Backhoe as an option (it's the only option that will work for now). Then right click on the sheet tab and select View Code. Then paste this code into it and save:


'Beginning of code-----

'Create global variable that will be used in Select Case statements in the subroutine called Update():
Public vlSel As Integer


'If there is a change in any cell in this sheet:
Private Sub Worksheet_Change(ByVal Target As Range)


'Check to see if the selection change is in column C
If Target.Column = 3 Then



If Range("C4").Value = "M-Series Loader/Backhoe" And Range("D4").Value = "Block Heater" Then

'Set vlSel variable (for Case in Update subroutine) value to whatever number you need
vlSel = 0

'Call Update subroutine
Update

End If

If Range("C4").Value = "M-Series Loader/Backhoe" And Range("D4").Value = "Radio" Then

'Set vlSel variable (for Case in Update subroutine) value to whatever number you need
vlSel = 1

'Call Update subroutine
Update

End If

If Range("C4").Value = "M-Series Loader/Backhoe" And Range("D4").Value = "Air Cond." Then

'Set vlSel variable (for Case in Update subroutine) value to whatever number you need
vlSel = 2

'Call Update subroutine
Update

End If

If Range("C4").Value = "M-Series Loader/Backhoe" Then

'If there is no selection in column D
If Range("D4").Value = "" Or Range("D4").Value = Null Then

'Set vlSel variable (for Case in Update subroutine) value to whatever number you need
vlSel = 3

'Call Update subroutine
Update

End If

End If





'Else if the column is D
ElseIf Target.Column = 4 Then



If Range("C4").Value = "M-Series Loader/Backhoe" And Range("D4").Value = "Block Heater" Then

'Set vlSel variable (for Case in Update subroutine) value to whatever number you need
vlSel = 0

'Call Update subroutine
Update

End If

If Range("C4").Value = "M-Series Loader/Backhoe" And Range("D4").Value = "Radio" Then

'Set vlSel variable (for Case in Update subroutine) value to whatever number you need
vlSel = 1

'Call Update subroutine
Update

End If

If Range("C4").Value = "M-Series Loader/Backhoe" And Range("D4").Value = "Air Cond." Then

'Set vlSel variable (for Case in Update subroutine) value to whatever number you need
vlSel = 2

'Call Update subroutine
Update

End If

If Range("C4").Value = "M-Series Loader/Backhoe" Then

'If there is no selection in column D
If Range("D4").Value = "" Or Range("D4").Value = Null Then

'Set vlSel variable (for Case in Update subroutine) value to whatever number you need
vlSel = 3

'Call Update subroutine
Update

End If

End If




End If 'Selection is in column C or D

'...you can add as many If statements as you need--hopefully you don't have a lot of data

End Sub


'The subroutine called Update:
Private Sub Update()

'Selects a Case based on the global count variable value
Select Case vlSel

'If vlSel = 0
Case 0

'Populate rows 1-4 in column H with text values
Range("H1") = 3

'Set number value in column E
Range("E4").Value = Range("H1").Value



'If vlSel = 1
Case 1


'Populate rows 1-4 in column H with text values
Range("H1") = 6

'Set number value in column E
Range("E4").Value = Range("H1").Value


'If vlSel = 2
Case 2

'Populate rows 1-4 in column H with text values
Range("H1") = 20

'Set number value in column E
Range("E4").Value = Range("H1").Value




'If vlSel = 3
Case 3


Range("G1") = "Block Heater"
Range("G2") = "Radio"
Range("G3") = "Air Cond."

Range("H1") = Empty



'Change validation list values (for a list in cell D4)
Range("D4").Select
With Selection.Validation

.Delete

'Populate list values based on a range (range F1:F8 in this example)
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$G$1:$G$4"

'Ignore blank cells in range
.IgnoreBlank = True

.InCellDropdown = True
.ShowInput = True
.ShowError = True

End With


'Set number value in column E
Range("E4").Value = Range("H1").Value


End Select



End Sub

'End of code-----


Now go into your sheet and select M-Series Loader/Backhoe from the list you created in C4.

Let me know if this is close to what you wanted.


Email me if you have questions,

~Susan

Collapse -

Hey Thanks! BUT...=)

by andrew.petry In reply to Some VBA to try....

I finally have a fully functional prep time calculator using drop down menus thru data validation. I have yet to figure out how to make it when a model is selected, the base prep time will automatically pull from my data sheet and just appear to the right of the selection. Right now the only way I can get that value pulled is thru another drop down that has just one option (the base prep time). It's really hard for me to explain so if I could e-mail you a copy of what I'm working on then you would better understand. Let me know if that is ok! Thanks for all of your help i'm going to try the vba code you've shown me anyway to see if it better suits what i'm tryin to do! THANKS AGAIN!

andrew

Collapse -

That'd be great...

by susan.forister In reply to Hey Thanks! BUT...=)

Sure! Send it to me--that would help me understand what you have and find a better solution.

~Susan

Collapse -

what is your e-mail address???

by andrew.petry In reply to That'd be great...

What is your e-mail address??? mine is andrew.petry@hopfequipment.com

Collapse -

Oooops!

by susan.forister In reply to what is your e-mail addre ...

Sorry Andrew--for some reason I thought you could see it on here--it's:

susan.forister@gmail.com

Hope I can help you out--I know how frustrating Excel (and Access) can be sometimes.

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums