Copy multiple cells from workbook a to workbook b keeping keeping the exact reference to the cell
I am building a catalog I have all data and costing listed in workbook1. Now I need to reference workbook2 the customer catalog, back to workbook1 so all data only needs to be updated in one place. When in the function box you type = in workbook2 and pick a cell in workbook1 you get the $ around the cell location holding the exact cell. The data in workbook1 is in columns, I would like to drag the cell down in workbook2 and have it change the reference to workbook1 to the next row and still have the $ exact reference. Any suggestion for coping locations in blocks and pasting it keeping the exact reference to all cells with the $, Thanks in advance.
microsoft-excel
asked Jan 15 at 22:52
PrecisedgePrecisedge
62
add a comment |
I am building a catalog I have all data and costing listed in workbook1. Now I need to reference workbook2 the customer catalog, back to workbook1 so all data only needs to be updated in one place. When in the function box you type = in workbook2 and pick a cell in workbook1 you get the $ around the cell location holding the exact cell. The data in workbook1 is in columns, I would like to drag the cell down in workbook2 and have it change the reference to workbook1 to the next row and still have the $ exact reference. Any suggestion for coping locations in blocks and pasting it keeping the exact reference to all cells with the $, Thanks in advance.
microsoft-excel
asked Jan 15 at 22:52
PrecisedgePrecisedge
62
I don’t understand this. What do you mean by “get the cells below the referenced cell and keep the $ exact reference in all cells”? Can you give an example? … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Jan 16 at 2:28
add a comment |
I am building a catalog I have all data and costing listed in workbook1. Now I need to reference workbook2 the customer catalog, back to workbook1 so all data only needs to be updated in one place. When in the function box you type = in workbook2 and pick a cell in workbook1 you get the $ around the cell location holding the exact cell. The data in workbook1 is in columns, I would like to drag the cell down in workbook2 and have it change the reference to workbook1 to the next row and still have the $ exact reference. Any suggestion for coping locations in blocks and pasting it keeping the exact reference to all cells with the $, Thanks in advance.
microsoft-excel
asked Jan 15 at 22:52
PrecisedgePrecisedge
62
I am building a catalog I have all data and costing listed in workbook1. Now I need to reference workbook2 the customer catalog, back to workbook1 so all data only needs to be updated in one place. When in the function box you type = in workbook2 and pick a cell in workbook1 you get the $ around the cell location holding the exact cell. The data in workbook1 is in columns, I would like to drag the cell down in workbook2 and have it change the reference to workbook1 to the next row and still have the $ exact reference. Any suggestion for coping locations in blocks and pasting it keeping the exact reference to all cells with the $, Thanks in advance.
microsoft-excel
microsoft-excel
asked Jan 15 at 22:52
PrecisedgePrecisedge
62
asked Jan 15 at 22:52
PrecisedgePrecisedge
62
edited Jan 16 at 18:34
Precisedge
asked Jan 15 at 22:52
PrecisedgePrecisedge
62
asked Jan 15 at 22:52
PrecisedgePrecisedge
62
asked Jan 15 at 22:52
PrecisedgePrecisedge
62
62
I don’t understand this. What do you mean by “get the cells below the referenced cell and keep the $ exact reference in all cells”? Can you give an example? … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Jan 16 at 2:28
add a comment |
I don’t understand this. What do you mean by “get the cells below the referenced cell and keep the $ exact reference in all cells”? Can you give an example? … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Jan 16 at 2:28
I don’t understand this. What do you mean by “get the cells below the referenced cell and keep the $ exact reference in all cells”? Can you give an example? … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Jan 16 at 2:28
I don’t understand this. What do you mean by “get the cells below the referenced cell and keep the $ exact reference in all cells”? Can you give an example? … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Jan 16 at 2:28
add a comment |
1 Answer
1
active
oldest
votes
You can use a range reference to the other workbook in combination wit INDEX
INDEX('[WB]WS!'A1:A10,ROW(A1), 1)
The row reference makes it possible to drag the function down.
(the a1:a10 should be with dollarsigns, but I cannot find the sign on my phone ;-) )
answered Jan 16 at 18:31
JoostJoost
6027
my formula in workbookB is ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 When I drag it down I get ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 when I drag it down in workbookb it stays at 8405 I need it to change to 8406 8407 and so on as I drag down.
– Precisedge
Jan 16 at 19:05
Sorry I misread the question
– Joost
Jan 17 at 7:39
add a comment |
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%2f1394727%2fcopy-multiple-cells-from-workbook-a-to-workbook-b-keeping-keeping-the-exact-refe%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use a range reference to the other workbook in combination wit INDEX
INDEX('[WB]WS!'A1:A10,ROW(A1), 1)
The row reference makes it possible to drag the function down.
(the a1:a10 should be with dollarsigns, but I cannot find the sign on my phone ;-) )
answered Jan 16 at 18:31
JoostJoost
6027
my formula in workbookB is ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 When I drag it down I get ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 when I drag it down in workbookb it stays at 8405 I need it to change to 8406 8407 and so on as I drag down.
– Precisedge
Jan 16 at 19:05
Sorry I misread the question
– Joost
Jan 17 at 7:39
add a comment |
You can use a range reference to the other workbook in combination wit INDEX
INDEX('[WB]WS!'A1:A10,ROW(A1), 1)
The row reference makes it possible to drag the function down.
(the a1:a10 should be with dollarsigns, but I cannot find the sign on my phone ;-) )
answered Jan 16 at 18:31
JoostJoost
6027
my formula in workbookB is ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 When I drag it down I get ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 when I drag it down in workbookb it stays at 8405 I need it to change to 8406 8407 and so on as I drag down.
– Precisedge
Jan 16 at 19:05
Sorry I misread the question
– Joost
Jan 17 at 7:39
add a comment |
You can use a range reference to the other workbook in combination wit INDEX
INDEX('[WB]WS!'A1:A10,ROW(A1), 1)
The row reference makes it possible to drag the function down.
(the a1:a10 should be with dollarsigns, but I cannot find the sign on my phone ;-) )
answered Jan 16 at 18:31
JoostJoost
6027
You can use a range reference to the other workbook in combination wit INDEX
INDEX('[WB]WS!'A1:A10,ROW(A1), 1)
The row reference makes it possible to drag the function down.
(the a1:a10 should be with dollarsigns, but I cannot find the sign on my phone ;-) )
answered Jan 16 at 18:31
JoostJoost
6027
edited Jan 17 at 7:53
answered Jan 16 at 18:31
JoostJoost
6027
answered Jan 16 at 18:31
JoostJoost
6027
answered Jan 16 at 18:31
JoostJoost
6027
6027
my formula in workbookB is ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 When I drag it down I get ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 when I drag it down in workbookb it stays at 8405 I need it to change to 8406 8407 and so on as I drag down.
– Precisedge
Jan 16 at 19:05
Sorry I misread the question
– Joost
Jan 17 at 7:39
add a comment |
my formula in workbookB is ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 When I drag it down I get ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 when I drag it down in workbookb it stays at 8405 I need it to change to 8406 8407 and so on as I drag down.
– Precisedge
Jan 16 at 19:05
Sorry I misread the question
– Joost
Jan 17 at 7:39
my formula in workbookB is ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 When I drag it down I get ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 when I drag it down in workbookb it stays at 8405 I need it to change to 8406 8407 and so on as I drag down.
– Precisedge
Jan 16 at 19:05
my formula in workbookB is ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 When I drag it down I get ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 ='[PE Data Spec Book 2019.xlsx]Product List'!$P$8405 when I drag it down in workbookb it stays at 8405 I need it to change to 8406 8407 and so on as I drag down.
– Precisedge
Jan 16 at 19:05
Sorry I misread the question
– Joost
Jan 17 at 7:39
Sorry I misread the question
– Joost
Jan 17 at 7:39
add a comment |
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.
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%2f1394727%2fcopy-multiple-cells-from-workbook-a-to-workbook-b-keeping-keeping-the-exact-refe%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
I don’t understand this. What do you mean by “get the cells below the referenced cell and keep the $ exact reference in all cells”? Can you give an example? … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
– Scott
Jan 16 at 2:28