Solved

Data validation in Excel

Posted on 2011-02-25
6
254 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

831 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