Bar-codes and Excel Spreadsheets
I've been asked by our warehouse guy to help him with an excel spreadsheet to increase his productivity. The problem is, I'm not what you would call an "excel expert". What he wants sounds very simple, I'm just not sure how to do it.
Right now he has a USB barcode scanner. When he scans to an excel spreadsheet he would like to scan the UPC code of an item and have it automatically change to our actual part number. Can this be done? Please let me know if you would like additional information
For Example:
UPC = 38568963772
Item# = AF5155
Edit:
this show what we are trying to scan the UPC codes into.
this is a simple two column file pulled from our system.
microsoft-excel barcode-scanner
add a comment |
I've been asked by our warehouse guy to help him with an excel spreadsheet to increase his productivity. The problem is, I'm not what you would call an "excel expert". What he wants sounds very simple, I'm just not sure how to do it.
Right now he has a USB barcode scanner. When he scans to an excel spreadsheet he would like to scan the UPC code of an item and have it automatically change to our actual part number. Can this be done? Please let me know if you would like additional information
For Example:
UPC = 38568963772
Item# = AF5155
Edit:
this show what we are trying to scan the UPC codes into.
this is a simple two column file pulled from our system.
microsoft-excel barcode-scanner
2
You would need a database of all your products UPC codes and their corresponding Item numbers, for the spreadsheet to reference. Access would probably be better suited for this task.
– wysiwyg
Mar 27 '17 at 22:08
@wysiwyg Thank you for the response. I do have a database ready to go. Just not sure how to reference in excel.
– Chris Malkiewicz
Mar 27 '17 at 22:11
Excel is extremely capable. So what you want is certainly doable depending on what type of database it is. Many tutorials online that explain how to get started
– Ramhound
Mar 27 '17 at 22:16
You can use vlookup but the result will be in a second column where you write the formula, it will not replace UPC, edit your question and write an example(your database sheet and the new sheet)
– yass
Mar 27 '17 at 22:18
@yass Thank you, I've added a couple images as I was unable to lay it out like I had hoped. Let me know if that is enough or additional information is needed.
– Chris Malkiewicz
Mar 27 '17 at 22:44
add a comment |
I've been asked by our warehouse guy to help him with an excel spreadsheet to increase his productivity. The problem is, I'm not what you would call an "excel expert". What he wants sounds very simple, I'm just not sure how to do it.
Right now he has a USB barcode scanner. When he scans to an excel spreadsheet he would like to scan the UPC code of an item and have it automatically change to our actual part number. Can this be done? Please let me know if you would like additional information
For Example:
UPC = 38568963772
Item# = AF5155
Edit:
this show what we are trying to scan the UPC codes into.
this is a simple two column file pulled from our system.
microsoft-excel barcode-scanner
I've been asked by our warehouse guy to help him with an excel spreadsheet to increase his productivity. The problem is, I'm not what you would call an "excel expert". What he wants sounds very simple, I'm just not sure how to do it.
Right now he has a USB barcode scanner. When he scans to an excel spreadsheet he would like to scan the UPC code of an item and have it automatically change to our actual part number. Can this be done? Please let me know if you would like additional information
For Example:
UPC = 38568963772
Item# = AF5155
Edit:
this show what we are trying to scan the UPC codes into.
this is a simple two column file pulled from our system.
microsoft-excel barcode-scanner
microsoft-excel barcode-scanner
edited Mar 28 '17 at 12:28
bertieb
5,672112542
5,672112542
asked Mar 27 '17 at 22:04
Chris MalkiewiczChris Malkiewicz
33
33
2
You would need a database of all your products UPC codes and their corresponding Item numbers, for the spreadsheet to reference. Access would probably be better suited for this task.
– wysiwyg
Mar 27 '17 at 22:08
@wysiwyg Thank you for the response. I do have a database ready to go. Just not sure how to reference in excel.
– Chris Malkiewicz
Mar 27 '17 at 22:11
Excel is extremely capable. So what you want is certainly doable depending on what type of database it is. Many tutorials online that explain how to get started
– Ramhound
Mar 27 '17 at 22:16
You can use vlookup but the result will be in a second column where you write the formula, it will not replace UPC, edit your question and write an example(your database sheet and the new sheet)
– yass
Mar 27 '17 at 22:18
@yass Thank you, I've added a couple images as I was unable to lay it out like I had hoped. Let me know if that is enough or additional information is needed.
– Chris Malkiewicz
Mar 27 '17 at 22:44
add a comment |
2
You would need a database of all your products UPC codes and their corresponding Item numbers, for the spreadsheet to reference. Access would probably be better suited for this task.
– wysiwyg
Mar 27 '17 at 22:08
@wysiwyg Thank you for the response. I do have a database ready to go. Just not sure how to reference in excel.
– Chris Malkiewicz
Mar 27 '17 at 22:11
Excel is extremely capable. So what you want is certainly doable depending on what type of database it is. Many tutorials online that explain how to get started
– Ramhound
Mar 27 '17 at 22:16
You can use vlookup but the result will be in a second column where you write the formula, it will not replace UPC, edit your question and write an example(your database sheet and the new sheet)
– yass
Mar 27 '17 at 22:18
@yass Thank you, I've added a couple images as I was unable to lay it out like I had hoped. Let me know if that is enough or additional information is needed.
– Chris Malkiewicz
Mar 27 '17 at 22:44
2
2
You would need a database of all your products UPC codes and their corresponding Item numbers, for the spreadsheet to reference. Access would probably be better suited for this task.
– wysiwyg
Mar 27 '17 at 22:08
You would need a database of all your products UPC codes and their corresponding Item numbers, for the spreadsheet to reference. Access would probably be better suited for this task.
– wysiwyg
Mar 27 '17 at 22:08
@wysiwyg Thank you for the response. I do have a database ready to go. Just not sure how to reference in excel.
– Chris Malkiewicz
Mar 27 '17 at 22:11
@wysiwyg Thank you for the response. I do have a database ready to go. Just not sure how to reference in excel.
– Chris Malkiewicz
Mar 27 '17 at 22:11
Excel is extremely capable. So what you want is certainly doable depending on what type of database it is. Many tutorials online that explain how to get started
– Ramhound
Mar 27 '17 at 22:16
Excel is extremely capable. So what you want is certainly doable depending on what type of database it is. Many tutorials online that explain how to get started
– Ramhound
Mar 27 '17 at 22:16
You can use vlookup but the result will be in a second column where you write the formula, it will not replace UPC, edit your question and write an example(your database sheet and the new sheet)
– yass
Mar 27 '17 at 22:18
You can use vlookup but the result will be in a second column where you write the formula, it will not replace UPC, edit your question and write an example(your database sheet and the new sheet)
– yass
Mar 27 '17 at 22:18
@yass Thank you, I've added a couple images as I was unable to lay it out like I had hoped. Let me know if that is enough or additional information is needed.
– Chris Malkiewicz
Mar 27 '17 at 22:44
@yass Thank you, I've added a couple images as I was unable to lay it out like I had hoped. Let me know if that is enough or additional information is needed.
– Chris Malkiewicz
Mar 27 '17 at 22:44
add a comment |
1 Answer
1
active
oldest
votes
In another place in the workbook (different sheet would be neatest), have a 2 column list of UPC code and part number.
Then, in the column next to where the UPC appears, use VLOOKUP:
=vlookup( [cell with UPC code] , [range with the two columns of data], 2, False)
Make sure the reference to the range is static, eg $a$3:$b$50, so if you fill the formula down the column of scanned codes the reference doesn't change.
Alternatively, if the part number needs to actually replace the UPC code, you'd need to write a macro to fire on cell changes, but this is a fair bit more work.
If it's a small database, you could program all of the UPC codes into the custom autocorrect. Obviously that could get unwieldy very quickly.
– Charles Burge
Mar 28 '17 at 1:24
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%2f1192943%2fbar-codes-and-excel-spreadsheets%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
In another place in the workbook (different sheet would be neatest), have a 2 column list of UPC code and part number.
Then, in the column next to where the UPC appears, use VLOOKUP:
=vlookup( [cell with UPC code] , [range with the two columns of data], 2, False)
Make sure the reference to the range is static, eg $a$3:$b$50, so if you fill the formula down the column of scanned codes the reference doesn't change.
Alternatively, if the part number needs to actually replace the UPC code, you'd need to write a macro to fire on cell changes, but this is a fair bit more work.
If it's a small database, you could program all of the UPC codes into the custom autocorrect. Obviously that could get unwieldy very quickly.
– Charles Burge
Mar 28 '17 at 1:24
add a comment |
In another place in the workbook (different sheet would be neatest), have a 2 column list of UPC code and part number.
Then, in the column next to where the UPC appears, use VLOOKUP:
=vlookup( [cell with UPC code] , [range with the two columns of data], 2, False)
Make sure the reference to the range is static, eg $a$3:$b$50, so if you fill the formula down the column of scanned codes the reference doesn't change.
Alternatively, if the part number needs to actually replace the UPC code, you'd need to write a macro to fire on cell changes, but this is a fair bit more work.
If it's a small database, you could program all of the UPC codes into the custom autocorrect. Obviously that could get unwieldy very quickly.
– Charles Burge
Mar 28 '17 at 1:24
add a comment |
In another place in the workbook (different sheet would be neatest), have a 2 column list of UPC code and part number.
Then, in the column next to where the UPC appears, use VLOOKUP:
=vlookup( [cell with UPC code] , [range with the two columns of data], 2, False)
Make sure the reference to the range is static, eg $a$3:$b$50, so if you fill the formula down the column of scanned codes the reference doesn't change.
Alternatively, if the part number needs to actually replace the UPC code, you'd need to write a macro to fire on cell changes, but this is a fair bit more work.
In another place in the workbook (different sheet would be neatest), have a 2 column list of UPC code and part number.
Then, in the column next to where the UPC appears, use VLOOKUP:
=vlookup( [cell with UPC code] , [range with the two columns of data], 2, False)
Make sure the reference to the range is static, eg $a$3:$b$50, so if you fill the formula down the column of scanned codes the reference doesn't change.
Alternatively, if the part number needs to actually replace the UPC code, you'd need to write a macro to fire on cell changes, but this is a fair bit more work.
answered Mar 27 '17 at 22:57
Sir AdelaideSir Adelaide
4,5972728
4,5972728
If it's a small database, you could program all of the UPC codes into the custom autocorrect. Obviously that could get unwieldy very quickly.
– Charles Burge
Mar 28 '17 at 1:24
add a comment |
If it's a small database, you could program all of the UPC codes into the custom autocorrect. Obviously that could get unwieldy very quickly.
– Charles Burge
Mar 28 '17 at 1:24
If it's a small database, you could program all of the UPC codes into the custom autocorrect. Obviously that could get unwieldy very quickly.
– Charles Burge
Mar 28 '17 at 1:24
If it's a small database, you could program all of the UPC codes into the custom autocorrect. Obviously that could get unwieldy very quickly.
– Charles Burge
Mar 28 '17 at 1:24
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%2f1192943%2fbar-codes-and-excel-spreadsheets%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
2
You would need a database of all your products UPC codes and their corresponding Item numbers, for the spreadsheet to reference. Access would probably be better suited for this task.
– wysiwyg
Mar 27 '17 at 22:08
@wysiwyg Thank you for the response. I do have a database ready to go. Just not sure how to reference in excel.
– Chris Malkiewicz
Mar 27 '17 at 22:11
Excel is extremely capable. So what you want is certainly doable depending on what type of database it is. Many tutorials online that explain how to get started
– Ramhound
Mar 27 '17 at 22:16
You can use vlookup but the result will be in a second column where you write the formula, it will not replace UPC, edit your question and write an example(your database sheet and the new sheet)
– yass
Mar 27 '17 at 22:18
@yass Thank you, I've added a couple images as I was unable to lay it out like I had hoped. Let me know if that is enough or additional information is needed.
– Chris Malkiewicz
Mar 27 '17 at 22:44