 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2006Drop-down list Populated by a Subset of a larger list
I'm trying to create a drop-down list (in Data Validation) that is populated by the subset of a larger list. The larger list is not sorted, nor can it be. If possible, I'd prefer not create a subset list elsewhere in the worksheet and have a list in Data Validation refer to it. Ideally, I'd like to enter a formula in the Source: box (after choosing List under Allow: in Data Validation) that would create a filtered list from the larger list. It's difficult for me to explain this well, but maybe an example will help: I have a table of baseball players, 20 rows by 3 columns (columns are Position, Name and Batting Avg.). I'd like to create a pull-down menu, below the main menu, with just first basemen in it (and let's assume there are 3 first basemen in the larger list). Any help is greatly appreciated, I've been struggling with this for a while. Hi! You have 2 choices: 1. sort the original list then you can write a formula that'll populate the drop down based on a players position. 2. create sublists of each position. >The larger list is not sorted, nor can it be. Kind of narrows down your options! Why can't you create a sorted copy of the larger list? Biff > I'm trying to create a drop-down list (in Data Validation) that is > populated [quoted text clipped - 16 lines] > Any help is greatly appreciated, I've been struggling with this for a > while. Thanks Biff, I'm using the second option right now: sub-lists of players. In fact, I'm using a modification of a formula I think you posted elsewhere on 'net! You are prolific! The first option would be more efficient, but I can't sort the larger list because: 1) it's a web query and 2) I need the workbook to be dynamic. If there is a way to create a sorted list dynamically (an array function that sorts referenced data?), that would be the answer, but I'm not aware of a way to do that. Any suggestions? Rich > Hi! > [quoted text clipped - 32 lines] > > Any help is greatly appreciated, I've been struggling with this for a > > while. Hi! It's possible to dynamically sort a list with formulas but I'm not so sure it would be any "better" than creating sublists as you have done. Can you give me a more thorough explanation of what you're wanting to do? Does the web query pull the same amount of data each time or does it vary? >I have a table of baseball players, 20 rows by 3 columns (columns are >Position, Name and Batting Avg.). I'd like to create a pull-down menu, >below >the main menu, with just first basemen in it (and let's assume there are 3 >first basemen in the larger list). What data do you want in the drop down, the first basemens names? I'm guessing that you also want to do this for any position? We might be able to get this to work! Biff > Thanks Biff, I'm using the second option right now: sub-lists of players. > In [quoted text clipped - 53 lines] >> > Any help is greatly appreciated, I've been struggling with this for a >> > while. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.