#1
|
|||
|
|||
Drop down box list based on response to another drop down box
I'm attempting to build an Excel spreadsheet that will perform some calculations for me based on inputs on several drop down boxes. The first drop down box contains a selection of pipe size, the second contains available pipe schedules (thicknesses) for the chosen pipe size. The available pipe schedules will depend on the chosen pipe size. Is this possible to do??
|
#2
|
|||
|
|||
Possible to do.... YES.
Will you be supplying a sample workbook to base any suggestions or direction on? Also, are you looking for formula or macro solution? Last edited by NoSparks; 04-10-2018 at 12:15 PM. Reason: added second question. |
#3
|
|||
|
|||
NoSparks, I understand formulas far more thoroughly than macros, but I'll go whichever way is more straight forward. I'm including an example sheet where I've got a drop down for the pipe size (NPS) and showing the column for the choice of schedule, which is dependent upon the pipe size chosen. The lower table shows the pipe sizes and the available schedules for each.
|
#4
|
||||
|
||||
Perhaps this will help http://www.contextures.com/xlDataVal02.html
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
I would use VBA in the WorkSheet_Change event for this.
Right click the sheet tab and select View Code. Hopefully I've commented things enough for you to follow. PS: are you actually using Excel 2003 ? |
#6
|
|||
|
|||
Have done such data validation pairs.
On hidden sheet is a table with 1st data validation list as header. The list of data validation values for 2nd data validation list is stored in according column of hidden table. You create a Dynamic Named Range, which returns datarange from one of columns depending on value selected in 1st data validation, and use this dynamic range as list source for 2nd data validation. NB! When you need to use this in a table (you select a pipe size in table row, and you have to select the schedule based on this selection in same row), then the Dynamic Named Range must depend from position in table too, i.e. from pipe size value selected in this particular row! |
#7
|
||||
|
||||
In the attached, is data validation in column B which refers to a dynamic named range called drpdwn2 as the list.
The formula was added to the named ranges while the active cell was in row 3: Code:
=OFFSET('Front Sheet'!$B$19:$M$19,MATCH('Front Sheet'!$A3,'Front Sheet'!$A$20:$A$34,0),0,,COUNTA(OFFSET('Front Sheet'!$B$19:$M$19,MATCH('Front Sheet'!$A3,'Front Sheet'!$A$20:$A$34,0),0))) |
#8
|
|||
|
|||
Quote:
|
#9
|
|||
|
|||
Yes! I have, however, given up riding my dinosaur to work and now use a horse and buggy!
|
#10
|
|||
|
|||
Quote:
|
#11
|
|||
|
|||
Quote:
|
#12
|
|||
|
|||
A general reply:
Thanks to all of you for trying to help an old guy (who recently gave up riding dinosaurs!). As soon as I get the sheet set up, there's one more step I need to overcome, but I'll try to handle it first. I'll post back up as to let anyone who is interested keep up with my progress. This is a "low priority" item that I'm working up just to help out the other engineers. I work for a company that designs custom API storage tanks and ASME pressure vessels. Hopefully, this worksheet can be used to calculate addition vessel/tank weight from their nozzles. It will also be a means to help our estimators with fabrication and shipping cost estimates. I really appreciate the help that is being offered from you guys, and I really, really need it! |
#13
|
||||
|
||||
Quote:
You should be able to hide the Stuff sheet Because you use Excel 2003 I've had to add an extra named range (NPS_Size) because Excel 2003 doesn't seem to like data validation referring directly to another sheet. |
#14
|
|||
|
|||
Quote:
And again, "Thanks!" EDIT: Yikes! I just opened your last sheet. The functionality of the front page is gone. It lists the values, but there's no way to change/select the values anymore. I'm beginning to wonder if my Excell program is coming undone. Last edited by Phideaux; 04-12-2018 at 08:18 AM. Reason: Additional info |
#15
|
|||
|
|||
No Phideaus, your Excel program isn't coming undone.... that's Excel 2003.
Things improved with the newer versions, but that doesn't help you right now. Tables weren't introduced until Excel 2007. Excel 2003 can't apply validation from another sheet unless you use named ranges. Manually giving each range a name and formulating when to use it... not for me. In the attached, the Schedules is moved to a separate sheet which can be hidden. The macro in Module1 only needed to be run once to name the ranges. The WorkSheet_Change event monitors the cells of 'Front Sheet' column A for changes and uses the same calculation as the NameRanges macro to determine 'on the fly' what range to use for validation in the adjacent column B cell. Don't know if this is of any use to you or not but at least have a look. |
Tags |
conditional, drop down list |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to import list from Excel into drop-down list into word | ahw | Word VBA | 43 | 02-28-2020 08:11 PM |
Drop down lists and pulling data from worksheet based on drop down selection | cjoyce73 | Excel | 5 | 07-17-2017 07:40 AM |
how to make sections hidden/appear based on selection in Cont. Ctrl. Drop-down list | Irimi-Ai | Word | 5 | 04-25-2017 09:31 AM |
Use different sumifs based on value in drop down. | Prondr | Excel | 3 | 12-20-2016 07:05 AM |
Dynamically changing drop-down list based on selection? (Word Form) | laurarem | Word | 1 | 02-21-2013 10:17 PM |