Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-27-2014, 12:10 PM
copwriter copwriter is offline Awkward dropdown list generation Windows 7 64bit Awkward dropdown list generation Office 2010 64bit
Novice
Awkward dropdown list generation
 
Join Date: Jun 2014
Posts: 4
copwriter is on a distinguished road
Default 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.
  1. The MasterList.xlsx file has to be open any time I am using a speadsheet that pulls data from it, or the dropdown lists are blank.
  2. I often have to convert the spreadsheets to PDF to send a patient's file to another provider. The PDF software will combine multiple files into one big PDF, converting files to PDF as necessary, but it hangs up when doing this with a file that pulled data from MasterList.xlsx. I can print the spreadsheet to PDF manually, but that takes an extra step and is inefficient.
  3. Editing any of the dropdowns invokes an error message of "You may not use references from other workbooks for Data Validation criteria." The list still works, but I always feel like I am courting disaster if this link breaks.

It seems like there should be a more elegant way of creating these dropdown lists. Can anyone suggest a better solution?
Reply With Quote
  #2  
Old 06-27-2014, 07:03 PM
whatsup whatsup is offline Awkward dropdown list generation Windows 7 64bit Awkward dropdown list generation Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 06-28-2014, 05:01 PM
copwriter copwriter is offline Awkward dropdown list generation Windows 7 64bit Awkward dropdown list generation Office 2010 64bit
Novice
Awkward dropdown list generation
 
Join Date: Jun 2014
Posts: 4
copwriter is on a distinguished road
Default

Quote:
Originally Posted by whatsup View Post
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.
Can you give me an example of one of these formulas?
Reply With Quote
  #4  
Old 06-28-2014, 05:59 PM
whatsup whatsup is offline Awkward dropdown list generation Windows 7 64bit Awkward dropdown list generation Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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"
Reply With Quote
Reply

Tags
cell references, drop down lists

Thread Tools
Display Modes


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
Awkward dropdown list generation 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:00 PM.


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