Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-10-2018, 11:33 AM
Phideaux Phideaux is offline Drop down box list based on response to another drop down box Windows 7 32bit Drop down box list based on response to another drop down box Office 2003
Novice
Drop down box list based on response to another drop down box
 
Join Date: Apr 2018
Posts: 8
Phideaux is on a distinguished road
Default 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??
Reply With Quote
  #2  
Old 04-10-2018, 12:13 PM
NoSparks NoSparks is offline Drop down box list based on response to another drop down box Windows 7 64bit Drop down box list based on response to another drop down box Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 792
NoSparks has a spectacular aura aboutNoSparks has a spectacular aura aboutNoSparks has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 04-10-2018, 12:31 PM
Phideaux Phideaux is offline Drop down box list based on response to another drop down box Windows 7 32bit Drop down box list based on response to another drop down box Office 2003
Novice
Drop down box list based on response to another drop down box
 
Join Date: Apr 2018
Posts: 8
Phideaux is on a distinguished road
Default

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.
Attached Files
File Type: xls Forum example.xls (29.0 KB, 11 views)
Reply With Quote
  #4  
Old 04-10-2018, 11:31 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Drop down box list based on response to another drop down box Windows 7 64bit Drop down box list based on response to another drop down box Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,431
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Perhaps this will help http://www.contextures.com/xlDataVal02.html
Reply With Quote
  #5  
Old 04-11-2018, 08:49 AM
NoSparks NoSparks is offline Drop down box list based on response to another drop down box Windows 7 64bit Drop down box list based on response to another drop down box Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 792
NoSparks has a spectacular aura aboutNoSparks has a spectacular aura aboutNoSparks has a spectacular aura about
Default

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 ?
Attached Files
File Type: xls Forum example_v2.xls (40.5 KB, 7 views)
Reply With Quote
  #6  
Old 04-11-2018, 11:09 PM
ArviLaanemets ArviLaanemets is offline Drop down box list based on response to another drop down box Windows 8 Drop down box list based on response to another drop down box Office 2016
Expert
 
Join Date: May 2017
Posts: 578
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

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!
Reply With Quote
  #7  
Old 04-12-2018, 06:26 AM
p45cal p45cal is offline Drop down box list based on response to another drop down box Windows 10 Drop down box list based on response to another drop down box Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 412
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

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)))
It is dynamic both in width and vertical position.
Attached Files
File Type: xls MSOfficeForums38742example.xls (29.5 KB, 6 views)
Reply With Quote
  #8  
Old 04-12-2018, 07:08 AM
Phideaux Phideaux is offline Drop down box list based on response to another drop down box Windows 7 32bit Drop down box list based on response to another drop down box Office 2003
Novice
Drop down box list based on response to another drop down box
 
Join Date: Apr 2018
Posts: 8
Phideaux is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
Pecoflyer, thanks for the link. However, it must be for a newer version of Excell, as I don't have the "Home" tab. It makes the rest of the directions inapplicable.
Reply With Quote
  #9  
Old 04-12-2018, 07:10 AM
Phideaux Phideaux is offline Drop down box list based on response to another drop down box Windows 7 32bit Drop down box list based on response to another drop down box Office 2003
Novice
Drop down box list based on response to another drop down box
 
Join Date: Apr 2018
Posts: 8
Phideaux is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
PS: are you actually using Excel 2003 ?
Yes! I have, however, given up riding my dinosaur to work and now use a horse and buggy!
Reply With Quote
  #10  
Old 04-12-2018, 07:42 AM
Phideaux Phideaux is offline Drop down box list based on response to another drop down box Windows 7 32bit Drop down box list based on response to another drop down box Office 2003
Novice
Drop down box list based on response to another drop down box
 
Join Date: Apr 2018
Posts: 8
Phideaux is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
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)))
It is dynamic both in width and vertical position.
I really like this approach simply because I am familiar with it! The question that I would like to ask is, can this approach draw values from another worksheet within the workbook? This would keep the front page "clean", as in no other tabular data being seen.
Reply With Quote
  #11  
Old 04-12-2018, 07:45 AM
Phideaux Phideaux is offline Drop down box list based on response to another drop down box Windows 7 32bit Drop down box list based on response to another drop down box Office 2003
Novice
Drop down box list based on response to another drop down box
 
Join Date: Apr 2018
Posts: 8
Phideaux is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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!
I'm really interested in what you've posted; I believe it is very much along the lines of what p45cal posted, with the exception that you've got the data table on a second or "hidden" sheet. This would be what I'd prefer to do so the front sheet can be kept visually "clean". Let me know if I'm understanding what you've proposed.
Reply With Quote
  #12  
Old 04-12-2018, 07:55 AM
Phideaux Phideaux is offline Drop down box list based on response to another drop down box Windows 7 32bit Drop down box list based on response to another drop down box Office 2003
Novice
Drop down box list based on response to another drop down box
 
Join Date: Apr 2018
Posts: 8
Phideaux is on a distinguished road
Default

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!
Reply With Quote
  #13  
Old 04-12-2018, 08:11 AM
p45cal p45cal is offline Drop down box list based on response to another drop down box Windows 10 Drop down box list based on response to another drop down box Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 412
p45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the roughp45cal is a jewel in the rough
Default

Quote:
Originally Posted by Phideaux View Post
can this approach draw values from another worksheet within the workbook?
Yes, see attached.
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.
Attached Files
File Type: xls MSOfficeForums38742example.xls (26.0 KB, 10 views)
Reply With Quote
  #14  
Old 04-12-2018, 08:14 AM
Phideaux Phideaux is offline Drop down box list based on response to another drop down box Windows 7 32bit Drop down box list based on response to another drop down box Office 2003
Novice
Drop down box list based on response to another drop down box
 
Join Date: Apr 2018
Posts: 8
Phideaux is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
Yes, see attached.
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.
Bingo! That was the initial issue that I was experiencing. While I could "hide" the tabular data, I'm prone to forget where I put it! (I'm old, ask my dinosaur)

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
Reply With Quote
  #15  
Old 04-12-2018, 06:07 PM
NoSparks NoSparks is offline Drop down box list based on response to another drop down box Windows 7 64bit Drop down box list based on response to another drop down box Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 792
NoSparks has a spectacular aura aboutNoSparks has a spectacular aura aboutNoSparks has a spectacular aura about
Default

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.
Attached Files
File Type: xls Forum example_v2a.xls (45.0 KB, 0 views)
Reply With Quote
Reply

Tags
conditional, drop down list

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Drop down box list based on response to another drop down box How to import list from Excel into drop-down list into word ahw Word VBA 43 02-28-2020 08:11 PM
Drop down box list based on response to another drop down box Drop down lists and pulling data from worksheet based on drop down selection cjoyce73 Excel 5 07-17-2017 07:40 AM
Drop down box list based on response to another drop down box 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
Drop down box list based on response to another drop down box Dynamically changing drop-down list based on selection? (Word Form) laurarem Word 1 02-21-2013 10:17 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 05:14 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft