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
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
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
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
ASKER
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
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
Hello,
check out the EE guidelines: https://www.experts-exchange.com/help.jsp?hi=405
I'm sure you'll do the right thing.
cheers, teylyn
check out the EE guidelines: https://www.experts-exchange.com/help.jsp?hi=405
I'm sure you'll do the right thing.
cheers, teylyn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
Chris