.bin Posted December 14, 2009 Share Posted December 14, 2009 Okay so I have an excel spreadsheet with a heap of drop-down boxes, using Data Validation to fetch the data from a range of cells. What I need to implement, are 10 schemes. eg: Scheme 1, Scheme 2, Scheme 3, and so forth.. And, compared on what's selected in the first drop down box, will affect what options/ranges of cells will be available in the other drop down boxes.. eg: If listbox 1 is selected as "Scheme 4", then use Cells a13 - a17, or if Scheme 6 is selected then use cells a17 - a20 Anyone know a steady way to do this? I guess you can say I'm a beginner. Link to comment Share on other sites More sharing options...
0 HammyJ Posted December 14, 2009 Share Posted December 14, 2009 You'll need to use the Developer tools within Excel. It'll be programmed in VBA which is a really easy language to pick up quickly. I've always found it's slightly easier and neater to create a form which will get the data from the spreadsheet itself. Should be pretty simple to do, just create a form with the relevant drop down boxes and have it fetch the data from ''nameOfSheet''. Link to comment Share on other sites More sharing options...
0 .bin Posted December 15, 2009 Author Share Posted December 15, 2009 I've done this. What I'm asking is I need the formula to change data validation depending IF a certain cell returns a function. I can do a whole heap of nested IF functions, in a formula, if need be. Link to comment Share on other sites More sharing options...
0 shihchiun Posted December 15, 2009 Share Posted December 15, 2009 You can use Select...End Select for this: Select Case <variable> Case <some value of the variable> <Do this> Case <some value of the variable> <Do this> End Select Link to comment Share on other sites More sharing options...
0 .bin Posted December 15, 2009 Author Share Posted December 15, 2009 Uhm, Can you gimme an example please? Link to comment Share on other sites More sharing options...
0 HammyJ Posted December 15, 2009 Share Posted December 15, 2009 'These are the coordinates for the rowsource Select Case Me.location.Text Dim x_min As Long, x_max As Long, Y As Long Case "England" 'Erase the previous selection Me.department.Text = "Please select a department:" 'Determine the row source as a function of the specific design of your database x_min = 3 Y = 1 Worksheets("deptData").Activate x_max = Cells(2, Y).Value + 2 'Assign the row source Me.department.RowSource = DataSheetName & "!" & Range(Cells(x_min, Y), Cells(x_max, Y)).Address Case "Scotland" Me.department.Text = "Please select a department:" x_min = 3 Y = 2 Worksheets("deptData").Activate x_max = Cells(2, Y).Value + 2 Me.department.RowSource = DataSheetName & "!" & Range(Cells(x_min, Y), Cells(x_max, Y)).Address Case "Ireland" Me.department.Text = "Please select a department:" x_min = 3 Y = 3 Worksheets("deptData").Activate x_max = Cells(2, Y).Value + 2 Me.department.RowSource = DataSheetName & "!" & Range(Cells(x_min, Y), Cells(x_max, Y)).Address Case "Wales" Me.department.Text = "Please select a department:" x_min = 3 Y = 4 Worksheets("deptData").Activate x_max = Cells(2, Y).Value + 2 Me.department.RowSource = DataSheetName & "!" & Range(Cells(x_min, Y), Cells(x_max, Y)).Address End Select Just pasted that from something I've just finished working on. It shows how the Select...Case statements work. In this example I have used it to select the row source range for another combo-box based on the value selected in another. Link to comment Share on other sites More sharing options...
Question
.bin
Okay so I have an excel spreadsheet with a heap of drop-down boxes, using Data Validation to fetch the data from a range of cells.
What I need to implement, are 10 schemes.
eg: Scheme 1, Scheme 2, Scheme 3, and so forth..
And, compared on what's selected in the first drop down box, will affect what options/ranges of cells
will be available in the other drop down boxes..
eg: If listbox 1 is selected as "Scheme 4", then use Cells a13 - a17, or if Scheme 6 is selected then use cells a17 - a20
Anyone know a steady way to do this? I guess you can say I'm a beginner.
Link to comment
Share on other sites
5 answers to this question
Recommended Posts