#1
|
|||
|
|||
Awkward dropdown list generation
I have a spreadsheet template I use to track vital signs, test scores, medication doses, and lab tests for the physician I work for. At the top of the columns designated for tests, medications, and labs, I have a dropdown list created by using the Data Validation function. The list that each dropdown pulls from resides on a separate spreadsheet called "MasterList.xlsx." The lists are in named ranges, so I can edit the lists more easily (which I have to do a lot).
I previously had the lists on a separate sheet in the same template, but this was problematic. I could add a new medication or lab test to the list in the template, but previous spreadsheets created from that template would not have the new information, requiring it be added manually and piecemeal. Using MasterList propagates the changes through every spreadsheet based on the template. The current system still has problems.
It seems like there should be a more elegant way of creating these dropdown lists. Can anyone suggest a better solution? |
#2
|
|||
|
|||
Hi
I don't know why pdfs hang up, but you definitely can't refer a dropdown list to another workbook if it's closed. But what you can do, is fetching the data from the closed workbook to a sheet of your workbook you are working with by ordninary formulas. This range then you can use for your dropdown, and excel will accept this. With your extern references in your workbook, excel will ask on opening, if the references shall be updated or not. |
#3
|
|||
|
|||
Quote:
|
#4
|
|||
|
|||
Well, I don't know what your workbook "MasterList.xlsx" looks like, for example:
While both workbooks are open enter in a cell: =[MasterList.xlsx]Sheet1!A1 and copy the formula as far as you expect having data in "MasterList.xlsx" When closing "MasterList.xlsx" excel will extend the formula with the complete path to "MasterList.xlsx" |
Tags |
cell references, drop down lists |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Dropdown list, Macro | shield5 | Excel Programming | 7 | 10-27-2013 01:51 AM |
VBA: How to place dropdown list next to text | YigalB | Word VBA | 0 | 08-11-2013 01:48 PM |
block selection in dropdown list | Intruder | Excel | 2 | 01-10-2013 10:20 AM |
dropdown list for documents | r_p_t_0 | Word | 2 | 12-18-2012 05:55 AM |
Dropdown list of email addresses | J Partridge | Outlook | 1 | 01-13-2011 06:37 AM |