Solved

Data validation in Excel

Posted on 2011-02-25
6
258 Views
Last Modified: 2012-05-11
Dear Experts.
I have one Excel file on the server where I store "basis" data. Now I want to use this data in another excel file also located on the server in the following way:

In Excel file A I want to have a listing in the range A1:A10. Currently I did the following:
I used the data validation and there the Listing and a named range which I located on a second hidden sheet in the same file. The named range in the hidden sheet I populated with data via a link to the range in the other excel file...it works but seems complicated.

My question:
Is there a way to link the named range in the "basic" file directly into the data validation listing of the other excel file? For my understanding it should be possible but I don't know how the link should look like and what more I have to consider that the data exchange will happen in the background between the two excel files without that the "user" will feel and see it?

thank you
Nils

0
Comment
Question by:Petersburg1
  • 2
  • 2
  • 2
6 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34977706
Refer to www.contextures.com/xldataval05.html ... but basically the alternate workbook MUST be open for it to work.

Chris
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34978128
Hello,

as far as I'm aware, the structure you are using (which is the same technique as described in the Contextures link Chris posted), i.e.

Source workbook range name >> target workbook range name >> target workbook data validation list

... is the only way to do it. Some functionality exchanging data between closed workbooks has been improved with Excel 2010, but I'm AFAIK data validation from external files still requires the double range name hop.

cheers, teylyn
0
 

Author Comment

by:Petersburg1
ID: 34978229
Hi,
thanks for the quick answer...too bad.
So you helped me both but it is not a real solution :-)
What shall I do?
Split the points? Or no points?
thanks
Nils
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34978252
Hello,

check out the EE guidelines: http://www.experts-exchange.com/help.jsp?hi=405

I'm sure you'll do the right thing.

cheers, teylyn
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 250 total points
ID: 34978331
Nils

IN general terms ... sometimes the answer is you can't do that ... which is a valid answer.  I indicated the only recognised method of using another workbook which requires it to be open if this is not practicable ... as seems to be implication then you are left with your implementation.

Basically therefore there is only one recognised method of linking directly as in teh contextures link and if having the reference workbook open is not acceptable then the answer is you can't do that!

Chris
0
 

Author Closing Comment

by:Petersburg1
ID: 35024700
thanks
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
My experience with Windows 10 over a one year period and suggestions for smooth operation
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question