How to retain internal connections (existing worksheet) in excel
I am having an Excel Master File, v1 and the data source for my list boxes and multi-list boxes are pointing to an external workbook, v2 instead of internally.
As a result, my conditional formatting logic, based on and data validation sources don't work.
The conditional formatting should look up local values, but instead look at V2. The error #REF appears instead of the range of cells A1:B2 which should originally appear
I have tried the following:
- Removing Existing Connection
- Change the Connection file to be this worksheet (v1)
After pressing refresh, none of the above solutions work.
- I have tried to change source to v1and break link, since apparently this is supposed to make the list boxes retain their original references.
I am unable to do so, even after having v2 literally open. This error message appears:
So my questions are:
- How do I prevent the references from automatically being updated when I send my Masterfile to another user?
2.Is there any code to prevent automatically changing the references from internal (this workbook) to external (v2?)
Thanks in advance!
microsoft-excel worksheet-function
asked Dec 3 at 5:57
udidosa
951
|
show 2 more comments
I am having an Excel Master File, v1 and the data source for my list boxes and multi-list boxes are pointing to an external workbook, v2 instead of internally.
As a result, my conditional formatting logic, based on and data validation sources don't work.
The conditional formatting should look up local values, but instead look at V2. The error #REF appears instead of the range of cells A1:B2 which should originally appear
I have tried the following:
- Removing Existing Connection
- Change the Connection file to be this worksheet (v1)
After pressing refresh, none of the above solutions work.
- I have tried to change source to v1and break link, since apparently this is supposed to make the list boxes retain their original references.
I am unable to do so, even after having v2 literally open. This error message appears:
So my questions are:
- How do I prevent the references from automatically being updated when I send my Masterfile to another user?
2.Is there any code to prevent automatically changing the references from internal (this workbook) to external (v2?)
Thanks in advance!
microsoft-excel worksheet-function
asked Dec 3 at 5:57
udidosa
951
1
A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list. The most IMPORTANT is for the drop down list to work, the workbook which contains the list must be open, in the same instance of Excel, when you use the drop down lists.
– Rajesh S
Dec 3 at 6:18
"A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list." Actually, the master list is stored in a hidden worksheet in the same workbook. therefore, it is always open.
– udidosa
Dec 3 at 6:25
In that case Un-hide the Sheet select the Master List and provide a NAME to it,,, then create Drop Down in other WBook and use full path as reference,, =C:folderMasterWb.xls!MasterListName. Here MasterListMName is the Named Range in Master Wbook!!
– Rajesh S
Dec 3 at 6:33
thanks! but I do not want to create a local reference on C drive. Since the Master file will be shared around to others, it will not work if it points to my local C drive
– udidosa
Dec 3 at 6:58
1
Then use Shared Folder over the Network or Server's Folder!
– Rajesh S
Dec 3 at 7:02
|
show 2 more comments
I am having an Excel Master File, v1 and the data source for my list boxes and multi-list boxes are pointing to an external workbook, v2 instead of internally.
As a result, my conditional formatting logic, based on and data validation sources don't work.
The conditional formatting should look up local values, but instead look at V2. The error #REF appears instead of the range of cells A1:B2 which should originally appear
I have tried the following:
- Removing Existing Connection
- Change the Connection file to be this worksheet (v1)
After pressing refresh, none of the above solutions work.
- I have tried to change source to v1and break link, since apparently this is supposed to make the list boxes retain their original references.
I am unable to do so, even after having v2 literally open. This error message appears:
So my questions are:
- How do I prevent the references from automatically being updated when I send my Masterfile to another user?
2.Is there any code to prevent automatically changing the references from internal (this workbook) to external (v2?)
Thanks in advance!
microsoft-excel worksheet-function
asked Dec 3 at 5:57
udidosa
951
I am having an Excel Master File, v1 and the data source for my list boxes and multi-list boxes are pointing to an external workbook, v2 instead of internally.
As a result, my conditional formatting logic, based on and data validation sources don't work.
The conditional formatting should look up local values, but instead look at V2. The error #REF appears instead of the range of cells A1:B2 which should originally appear
I have tried the following:
- Removing Existing Connection
- Change the Connection file to be this worksheet (v1)
After pressing refresh, none of the above solutions work.
- I have tried to change source to v1and break link, since apparently this is supposed to make the list boxes retain their original references.
I am unable to do so, even after having v2 literally open. This error message appears:
So my questions are:
- How do I prevent the references from automatically being updated when I send my Masterfile to another user?
2.Is there any code to prevent automatically changing the references from internal (this workbook) to external (v2?)
Thanks in advance!
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Dec 3 at 5:57
udidosa
951
asked Dec 3 at 5:57
udidosa
951
asked Dec 3 at 5:57
udidosa
951
asked Dec 3 at 5:57
udidosa
951
asked Dec 3 at 5:57
udidosa
951
951
1
A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list. The most IMPORTANT is for the drop down list to work, the workbook which contains the list must be open, in the same instance of Excel, when you use the drop down lists.
– Rajesh S
Dec 3 at 6:18
"A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list." Actually, the master list is stored in a hidden worksheet in the same workbook. therefore, it is always open.
– udidosa
Dec 3 at 6:25
In that case Un-hide the Sheet select the Master List and provide a NAME to it,,, then create Drop Down in other WBook and use full path as reference,, =C:folderMasterWb.xls!MasterListName. Here MasterListMName is the Named Range in Master Wbook!!
– Rajesh S
Dec 3 at 6:33
thanks! but I do not want to create a local reference on C drive. Since the Master file will be shared around to others, it will not work if it points to my local C drive
– udidosa
Dec 3 at 6:58
1
Then use Shared Folder over the Network or Server's Folder!
– Rajesh S
Dec 3 at 7:02
|
show 2 more comments
1
A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list. The most IMPORTANT is for the drop down list to work, the workbook which contains the list must be open, in the same instance of Excel, when you use the drop down lists.
– Rajesh S
Dec 3 at 6:18
"A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list." Actually, the master list is stored in a hidden worksheet in the same workbook. therefore, it is always open.
– udidosa
Dec 3 at 6:25
In that case Un-hide the Sheet select the Master List and provide a NAME to it,,, then create Drop Down in other WBook and use full path as reference,, =C:folderMasterWb.xls!MasterListName. Here MasterListMName is the Named Range in Master Wbook!!
– Rajesh S
Dec 3 at 6:33
thanks! but I do not want to create a local reference on C drive. Since the Master file will be shared around to others, it will not work if it points to my local C drive
– udidosa
Dec 3 at 6:58
1
Then use Shared Folder over the Network or Server's Folder!
– Rajesh S
Dec 3 at 7:02
1
1
A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list. The most IMPORTANT is for the drop down list to work, the workbook which contains the list must be open, in the same instance of Excel, when you use the drop down lists.
– Rajesh S
Dec 3 at 6:18
A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list. The most IMPORTANT is for the drop down list to work, the workbook which contains the list must be open, in the same instance of Excel, when you use the drop down lists.
– Rajesh S
Dec 3 at 6:18
"A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list." Actually, the master list is stored in a hidden worksheet in the same workbook. therefore, it is always open.
– udidosa
Dec 3 at 6:25
"A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list." Actually, the master list is stored in a hidden worksheet in the same workbook. therefore, it is always open.
– udidosa
Dec 3 at 6:25
In that case Un-hide the Sheet select the Master List and provide a NAME to it,,, then create Drop Down in other WBook and use full path as reference,, =C:folderMasterWb.xls!MasterListName. Here MasterListMName is the Named Range in Master Wbook!!
– Rajesh S
Dec 3 at 6:33
In that case Un-hide the Sheet select the Master List and provide a NAME to it,,, then create Drop Down in other WBook and use full path as reference,, =C:folderMasterWb.xls!MasterListName. Here MasterListMName is the Named Range in Master Wbook!!
– Rajesh S
Dec 3 at 6:33
thanks! but I do not want to create a local reference on C drive. Since the Master file will be shared around to others, it will not work if it points to my local C drive
– udidosa
Dec 3 at 6:58
thanks! but I do not want to create a local reference on C drive. Since the Master file will be shared around to others, it will not work if it points to my local C drive
– udidosa
Dec 3 at 6:58
1
1
Then use Shared Folder over the Network or Server's Folder!
– Rajesh S
Dec 3 at 7:02
Then use Shared Folder over the Network or Server's Folder!
– Rajesh S
Dec 3 at 7:02
|
show 2 more comments
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1380319%2fhow-to-retain-internal-connections-existing-worksheet-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1380319%2fhow-to-retain-internal-connections-existing-worksheet-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list. The most IMPORTANT is for the drop down list to work, the workbook which contains the list must be open, in the same instance of Excel, when you use the drop down lists.
– Rajesh S
Dec 3 at 6:18
"A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list." Actually, the master list is stored in a hidden worksheet in the same workbook. therefore, it is always open.
– udidosa
Dec 3 at 6:25
In that case Un-hide the Sheet select the Master List and provide a NAME to it,,, then create Drop Down in other WBook and use full path as reference,, =C:folderMasterWb.xls!MasterListName. Here MasterListMName is the Named Range in Master Wbook!!
– Rajesh S
Dec 3 at 6:33
thanks! but I do not want to create a local reference on C drive. Since the Master file will be shared around to others, it will not work if it points to my local C drive
– udidosa
Dec 3 at 6:58
1
Then use Shared Folder over the Network or Server's Folder!
– Rajesh S
Dec 3 at 7:02