• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

Data validation in Excel

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
Petersburg1
Asked:
Petersburg1
  • 2
  • 2
  • 2
1 Solution
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Refer to www.contextures.com/xldataval05.html ... but basically the alternate workbook MUST be open for it to work.

Chris
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Petersburg1Author Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
 
Petersburg1Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now