Excel - Enter B/code and bring up correct product from second sheet

Forums › Desktop computing › Excel - Enter B/code and bring up correct product from second sheet

Morgenmuffel

631 posts

Ultimate Geek

Lifetime subscriber

#191385 2-Feb-2016 20:07

Ok currently at work i have a spreadsheet that i need to enter certain products in each day once they are delivered

Currently

I have dropdown lists on the main page, linking to the second sheet

How it works

I select the Correct Supplier, and the next cell comes up with the product names (and nothing else) and I select the correct product name, and all the product details including b/code are displayed,

unfortunately a lot of products have the same name or very similar names and it is impossible to tell from the product name which is which, where there are two identical product names i often have to reselect the alternate choice to get the correct product and b/code

Once all the days products are enetered I print the page

What I want

(as there is a b/code scanner attached to my computer)

Scan in the b/code press enter and the details come up, this should be simple, but as i have never made a spreadsheet unless i have been forced to, I can't figure out how to do this, the dropdown lists was right at the edge of my ability, but having a user input is throwing me completely, i can't seem to get the formulae to work.

Any help would be appreciated, Or a link to a coherent tutorial, my terminology is bad so i am probably looking for the wrong thing, I also have a pounding headache and backache after taking a tumble today so i am off to bed and hopefully this will make more sense in the morning

Notes

The product details are basically in a list like the below, (the supplier details aren't overly essential, i was planning to just add them in a third column of the product details to make the query easier)

Widget - 94000123456789

sprocket - 9400098765432

gadget - 9400045362718

'We love to buy books because we believe we’re buying the time to read them.' WARREN ZEVON

GregV

928 posts

Ultimate Geek

#1483878 2-Feb-2016 20:13

vlookup. https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

ANglEAUT

2326 posts

Uber Geek

Trusted

Lifetime subscriber

#1483941 2-Feb-2016 21:38

For basic functionality, VLOOKUP() will do the trick.

Watch out for the following

In your lookup table, the bar code value MUST be the left most value
Sort your bar codes in ascending order
In your VLOOKUP(), as the last parameter, specify that you want an exact match.
Consider that you will NOT find your value. Thus use error correction: =IFERR(VLOOKUP(bla,blah,blah),"No bar code found")

Here is a nice tutorial from Excel Tactics to get you started.

If you have the stomach for it, rather use an INDEX-MATCH-MATCH formula as explained on the mbaexcel website. With INDEX-MATCH-MATCH your lookup value can be anywhere in the lookup array and you can return results horizontally or vertically. Very useful if you can't re-arrange your data to use only the left most column for lookup.

Edit - Let me know if you want more help.

Please keep this GZ community vibrant by contributing in a constructive & respectful manner.

MadEngineer

4291 posts

Uber Geek

Trusted

#1484108 3-Feb-2016 09:04

Out of interest, does the likes of MYOB support this?

Perhaps your existing software has an add-on inventory package

You're not on Atlantis anymore, Duncan Idaho.

Morgenmuffel

631 posts

Ultimate Geek

Lifetime subscriber

#1484635 3-Feb-2016 17:59

Ok, this was the code i was using (I have a header row)

=VLOOKUP(A2,Sheet2!A1:B44,2,FALSE)

After having a read of the tutorials, I finally clicked what my problem was, i was using commas instead of semi colons, and my testing value was number 45 in my list and I had set my range to only 43 values

So the below sort of works

=VLOOKUP(A2;Sheet2!$A$1:$B$45;2;FALSE)

Obviously it doesn't handle missing values yet, but i am working on it (thanks for the code on that guys),

'We love to buy books because we believe we’re buying the time to read them.' WARREN ZEVON

Morgenmuffel

631 posts

Ultimate Geek

Lifetime subscriber

#1484664 3-Feb-2016 18:20

Ok so code now works correctly

=IFERROR(VLOOKUP(A4;Sheet2!$A$1:$B$47;2;FALSE);"Barcode doesn't exist")

Next question, if I don't enter anything into the b/code field how do I stop it saying anything

My main page looks a bit like this now

94000123456789 - Widget

9400098765432 -sprocket

940004536271 - gadget

940004531945 - Barcode Invalid

- Barcode Invalid

There must be some sort of if input < 2 Chars display nothing

So would my code be something like this? obviously i am making up the function names, but you get where I am heading?

=IFERROR(VLOOKUP(A4;Sheet2!$A$1:$B$47;2;FALSE);(IF_LENGTH_INPT >2 then "Barcode doesn't exist" Else ""))

'We love to buy books because we believe we’re buying the time to read them.' WARREN ZEVON

Morgenmuffel

631 posts

Ultimate Geek

Lifetime subscriber

#1484711 3-Feb-2016 18:46

Ok this seems to do everything

=IF(ISBLANK(A2);"";(IFERROR(VLOOKUP(A2;Sheet2!$A$1:$B$47;2;FALSE);"Barcode doesn't exist")))

Now all i have to do is do a big bit of testing, Please tell me if you see a glaring error in it

'We love to buy books because we believe we’re buying the time to read them.' WARREN ZEVON

ANglEAUT

2326 posts

Uber Geek

Trusted

Lifetime subscriber

#1484907 3-Feb-2016 23:56

Morgenmuffel: Please tell me if you see a glaring error in it

Looking good.

Please keep this GZ community vibrant by contributing in a constructive & respectful manner.

Sharesies

Trade NZ and US shares and funds with Sharesies (affiliate link).

bazzer

3438 posts

Uber Geek

Trusted

#1484912 4-Feb-2016 00:38

IcI:

For basic functionality, VLOOKUP() will do the trick.

Watch out for the following

In your lookup table, the bar code value MUST be the left most value
Sort your bar codes in ascending order
In your VLOOKUP(), as the last parameter, specify that you want an exact match.
Consider that you will NOT find your value. Thus use error correction: =IFERR(VLOOKUP(bla,blah,blah),"No bar code found")

Here is a nice tutorial from Excel Tactics to get you started.

If you have the stomach for it, rather use an INDEX-MATCH-MATCH formula as explained on the mbaexcel website. With INDEX-MATCH-MATCH your lookup value can be anywhere in the lookup array and you can return results horizontally or vertically. Very useful if you can't re-arrange your data to use only the left most column for lookup.

Edit - Let me know if you want more help.

If you're using "Exact match" the records don't have to be in any specific order.

MadEngineer

4291 posts

Uber Geek

Trusted

#1485147 4-Feb-2016 12:48

couple of tips when using vlookups: sometimes when coping and pasting data into excel the data can be pasted as text or with spaces that will break the vlookup. simple trick is to place the number 1 into any random cell, copy it, then select all the data and paste-special-multiply it. Easier than copying, pasting special as values only, then cutting the data back to the original location.

don't forget to "F4" the table range

You're not on Atlantis anymore, Duncan Idaho.

bazzer

3438 posts

Uber Geek

Trusted

#1485459 4-Feb-2016 21:50

Plus I really encourage people to us INDEX-MATCH. It's much more useful, and not really any harder to get your head around.

Morgenmuffel

631 posts

Ultimate Geek

Lifetime subscriber

#1488295 9-Feb-2016 19:35

Thanks all it is working fine for what I need now.

'We love to buy books because we believe we’re buying the time to read them.' WARREN ZEVON