How to make addresses populate from one spreadsheet to another
I have two excel sheets of store #'s/event details and another one of all the store number addresses. Not every store gets an event so just copying and pasting all the addresses wouldn't be an option. Is there a formula I can put in so all the store numbers used in the events excel can populate out the addresses sitting in the other excel without individually having to copy and paste?
Example:
Worksheet A
Store # - 7
Name - Store A
Event Date - Jan 1
Address - blank
Store # - 12
Name - Store B
Event Date - Jan 2
Worksheet B
Store # - 7
Address - 123 Happy St
Store # - 8
Address - 456 Smile Rd
Store # - 9
Address - 987 Tuesday Ln
Store # - 10
Address - 234 Monday Rd
Store # - 11
Address - 456 Wednesday Ln
microsoft-excel worksheet-function
edited Dec 11 at 2:33
teylyn
16.9k22539
asked Dec 11 at 1:15
Alexa Gallegos
1
add a comment |
I have two excel sheets of store #'s/event details and another one of all the store number addresses. Not every store gets an event so just copying and pasting all the addresses wouldn't be an option. Is there a formula I can put in so all the store numbers used in the events excel can populate out the addresses sitting in the other excel without individually having to copy and paste?
Example:
Worksheet A
Store # - 7
Name - Store A
Event Date - Jan 1
Address - blank
Store # - 12
Name - Store B
Event Date - Jan 2
Worksheet B
Store # - 7
Address - 123 Happy St
Store # - 8
Address - 456 Smile Rd
Store # - 9
Address - 987 Tuesday Ln
Store # - 10
Address - 234 Monday Rd
Store # - 11
Address - 456 Wednesday Ln
microsoft-excel worksheet-function
edited Dec 11 at 2:33
teylyn
16.9k22539
asked Dec 11 at 1:15
Alexa Gallegos
1
Try index() & match() ref and share where you get stuck. ( :
– p._phidot_
Dec 11 at 1:34
Is your data formatted the same way as your example? If so, reformatting your data into a table is probably a good first step.
– Excellll
Dec 12 at 15:11
add a comment |
I have two excel sheets of store #'s/event details and another one of all the store number addresses. Not every store gets an event so just copying and pasting all the addresses wouldn't be an option. Is there a formula I can put in so all the store numbers used in the events excel can populate out the addresses sitting in the other excel without individually having to copy and paste?
Example:
Worksheet A
Store # - 7
Name - Store A
Event Date - Jan 1
Address - blank
Store # - 12
Name - Store B
Event Date - Jan 2
Worksheet B
Store # - 7
Address - 123 Happy St
Store # - 8
Address - 456 Smile Rd
Store # - 9
Address - 987 Tuesday Ln
Store # - 10
Address - 234 Monday Rd
Store # - 11
Address - 456 Wednesday Ln
microsoft-excel worksheet-function
edited Dec 11 at 2:33
teylyn
16.9k22539
asked Dec 11 at 1:15
Alexa Gallegos
1
I have two excel sheets of store #'s/event details and another one of all the store number addresses. Not every store gets an event so just copying and pasting all the addresses wouldn't be an option. Is there a formula I can put in so all the store numbers used in the events excel can populate out the addresses sitting in the other excel without individually having to copy and paste?
Example:
Worksheet A
Store # - 7
Name - Store A
Event Date - Jan 1
Address - blank
Store # - 12
Name - Store B
Event Date - Jan 2
Worksheet B
Store # - 7
Address - 123 Happy St
Store # - 8
Address - 456 Smile Rd
Store # - 9
Address - 987 Tuesday Ln
Store # - 10
Address - 234 Monday Rd
Store # - 11
Address - 456 Wednesday Ln
microsoft-excel worksheet-function
microsoft-excel worksheet-function
edited Dec 11 at 2:33
teylyn
16.9k22539
asked Dec 11 at 1:15
Alexa Gallegos
1
edited Dec 11 at 2:33
teylyn
16.9k22539
asked Dec 11 at 1:15
Alexa Gallegos
1
edited Dec 11 at 2:33
teylyn
16.9k22539
edited Dec 11 at 2:33
teylyn
16.9k22539
edited Dec 11 at 2:33
teylyn
16.9k22539
16.9k22539
asked Dec 11 at 1:15
Alexa Gallegos
1
asked Dec 11 at 1:15
Alexa Gallegos
1
asked Dec 11 at 1:15
Alexa Gallegos
1
1
Try index() & match() ref and share where you get stuck. ( :
– p._phidot_
Dec 11 at 1:34
Is your data formatted the same way as your example? If so, reformatting your data into a table is probably a good first step.
– Excellll
Dec 12 at 15:11
add a comment |
Try index() & match() ref and share where you get stuck. ( :
– p._phidot_
Dec 11 at 1:34
Is your data formatted the same way as your example? If so, reformatting your data into a table is probably a good first step.
– Excellll
Dec 12 at 15:11
Try index() & match() ref and share where you get stuck. ( :
– p._phidot_
Dec 11 at 1:34
Try index() & match() ref and share where you get stuck. ( :
– p._phidot_
Dec 11 at 1:34
Is your data formatted the same way as your example? If so, reformatting your data into a table is probably a good first step.
– Excellll
Dec 12 at 15:11
Is your data formatted the same way as your example? If so, reformatting your data into a table is probably a good first step.
– Excellll
Dec 12 at 15:11
add a comment |
2 Answers
2
active
oldest
votes
If you change the layout of the store list to a simple, flat table with columns for each data item and the store number in the first column, you can use a very simple Vlookup function to look up address, city and any other data you store in that table.
If cell A1 in Worksheet A contains the store number, then you can use this formula to return the address, the 2nd column in the table. To return the City, change the 2 to a 3.
=vlookup(A1,'Worksheet B'!$A$1:$C$4,2,false)
answered Dec 11 at 2:39
teylyn
16.9k22539
add a comment |
First step is probably to get worksheet B in a usable format. The list-type format shown is terrible for processing in a spreadsheet.
If you can't change the format for worksheet B (e.g. by modifying the software generating the list), you can generate a new flat table automatically in a new sheet:
Screenshot of excel sheet
Formulas are:
Column E: =MID(INDIRECT("'Worksheet B'!R"&ROW()*3-5&"C1";FALSE);11;100)
Column F: =MID(INDIRECT("'Worksheet B'!R"&ROW()*3-4&"C1";FALSE);11;100)
Then use VLOOKUP as suggested by Teylyn. Ideally, you should also transform Worksheet A to a table format, which will make it much easier to use VLOOKUP to get the address.
answered Dec 11 at 9:33
manscher
12
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%2f1382484%2fhow-to-make-addresses-populate-from-one-spreadsheet-to-another%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you change the layout of the store list to a simple, flat table with columns for each data item and the store number in the first column, you can use a very simple Vlookup function to look up address, city and any other data you store in that table.
If cell A1 in Worksheet A contains the store number, then you can use this formula to return the address, the 2nd column in the table. To return the City, change the 2 to a 3.
=vlookup(A1,'Worksheet B'!$A$1:$C$4,2,false)
answered Dec 11 at 2:39
teylyn
16.9k22539
add a comment |
If you change the layout of the store list to a simple, flat table with columns for each data item and the store number in the first column, you can use a very simple Vlookup function to look up address, city and any other data you store in that table.
If cell A1 in Worksheet A contains the store number, then you can use this formula to return the address, the 2nd column in the table. To return the City, change the 2 to a 3.
=vlookup(A1,'Worksheet B'!$A$1:$C$4,2,false)
answered Dec 11 at 2:39
teylyn
16.9k22539
add a comment |
If you change the layout of the store list to a simple, flat table with columns for each data item and the store number in the first column, you can use a very simple Vlookup function to look up address, city and any other data you store in that table.
If cell A1 in Worksheet A contains the store number, then you can use this formula to return the address, the 2nd column in the table. To return the City, change the 2 to a 3.
=vlookup(A1,'Worksheet B'!$A$1:$C$4,2,false)
answered Dec 11 at 2:39
teylyn
16.9k22539
If you change the layout of the store list to a simple, flat table with columns for each data item and the store number in the first column, you can use a very simple Vlookup function to look up address, city and any other data you store in that table.
If cell A1 in Worksheet A contains the store number, then you can use this formula to return the address, the 2nd column in the table. To return the City, change the 2 to a 3.
=vlookup(A1,'Worksheet B'!$A$1:$C$4,2,false)
answered Dec 11 at 2:39
teylyn
16.9k22539
answered Dec 11 at 2:39
teylyn
16.9k22539
answered Dec 11 at 2:39
teylyn
16.9k22539
answered Dec 11 at 2:39
teylyn
16.9k22539
16.9k22539
add a comment |
add a comment |
First step is probably to get worksheet B in a usable format. The list-type format shown is terrible for processing in a spreadsheet.
If you can't change the format for worksheet B (e.g. by modifying the software generating the list), you can generate a new flat table automatically in a new sheet:
Screenshot of excel sheet
Formulas are:
Column E: =MID(INDIRECT("'Worksheet B'!R"&ROW()*3-5&"C1";FALSE);11;100)
Column F: =MID(INDIRECT("'Worksheet B'!R"&ROW()*3-4&"C1";FALSE);11;100)
Then use VLOOKUP as suggested by Teylyn. Ideally, you should also transform Worksheet A to a table format, which will make it much easier to use VLOOKUP to get the address.
answered Dec 11 at 9:33
manscher
12
add a comment |
First step is probably to get worksheet B in a usable format. The list-type format shown is terrible for processing in a spreadsheet.
If you can't change the format for worksheet B (e.g. by modifying the software generating the list), you can generate a new flat table automatically in a new sheet:
Screenshot of excel sheet
Formulas are:
Column E: =MID(INDIRECT("'Worksheet B'!R"&ROW()*3-5&"C1";FALSE);11;100)
Column F: =MID(INDIRECT("'Worksheet B'!R"&ROW()*3-4&"C1";FALSE);11;100)
Then use VLOOKUP as suggested by Teylyn. Ideally, you should also transform Worksheet A to a table format, which will make it much easier to use VLOOKUP to get the address.
answered Dec 11 at 9:33
manscher
12
add a comment |
First step is probably to get worksheet B in a usable format. The list-type format shown is terrible for processing in a spreadsheet.
If you can't change the format for worksheet B (e.g. by modifying the software generating the list), you can generate a new flat table automatically in a new sheet:
Screenshot of excel sheet
Formulas are:
Column E: =MID(INDIRECT("'Worksheet B'!R"&ROW()*3-5&"C1";FALSE);11;100)
Column F: =MID(INDIRECT("'Worksheet B'!R"&ROW()*3-4&"C1";FALSE);11;100)
Then use VLOOKUP as suggested by Teylyn. Ideally, you should also transform Worksheet A to a table format, which will make it much easier to use VLOOKUP to get the address.
answered Dec 11 at 9:33
manscher
12
First step is probably to get worksheet B in a usable format. The list-type format shown is terrible for processing in a spreadsheet.
If you can't change the format for worksheet B (e.g. by modifying the software generating the list), you can generate a new flat table automatically in a new sheet:
Screenshot of excel sheet
Formulas are:
Column E: =MID(INDIRECT("'Worksheet B'!R"&ROW()*3-5&"C1";FALSE);11;100)
Column F: =MID(INDIRECT("'Worksheet B'!R"&ROW()*3-4&"C1";FALSE);11;100)
Then use VLOOKUP as suggested by Teylyn. Ideally, you should also transform Worksheet A to a table format, which will make it much easier to use VLOOKUP to get the address.
answered Dec 11 at 9:33
manscher
12
answered Dec 11 at 9:33
manscher
12
answered Dec 11 at 9:33
manscher
12
answered Dec 11 at 9:33
manscher
12
12
add a comment |
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.
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%2f1382484%2fhow-to-make-addresses-populate-from-one-spreadsheet-to-another%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
Try index() & match() ref and share where you get stuck. ( :
– p._phidot_
Dec 11 at 1:34
Is your data formatted the same way as your example? If so, reformatting your data into a table is probably a good first step.
– Excellll
Dec 12 at 15:11