Solved

Data validation in Excel

Posted on 2011-02-25
6
250 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now