On Sheet 1 I have a long pricing list where there are material numbers and bracket prices for each material number.
On Sheet 2 I would like to enter 5 digit UPC codes and have the bracket prices auto populate for that UPC code.
Example:
Sheet 1
Long Material Number
12345678904
Old Material
12345678901
Material Number
123456789
Sheet 2
UPC
56789
I tried using VLOOKUP but that only works if upc and material number are both 5 digits.
Example.xlsxCan anyone help me out?
With first number in sheet2!A3:
Type in sheet2!B3 then copy down and accross:
Confirm with holding both Ctrl-Shift, then hit Enter
Please Login or Register to view this content.
Or
Enter only.Please Login or Register to view this content.
Quang PT
I don't know if this is what you want but it appears like the sheet2 that you are referring to is a table in sheet1. I inserted a column A and used the mid function to extract the upc code and colored the font white to hide the extracted number then used the vlookup function to populate the table sheet2.
Yes it will. we just have to figure out how...
If I have helped you, PLEASE click the * and add to my Rep.
Also, if the problem is SOLVED please mark it as so.
Wow that works, awesome, thank you.
Is There a way for it to search all 3 material numbers (since upc could match one of three material numbers, as long as the 5 digits that i enter is anywhere within any 3 material numers) and return all 3 bracket prices if match is found within one of 3 material numbers?
Also if upc is not found to return "0"
Thank you both formulas work, but both formulas work for bracket 1 price. How can I make these formulas to also show bracket 2 and bracket 3?
And can they also search all 3 material numbers?
I don't understand why it has to search all 3 material numbers, when the UPC code is the same in all three. The the attached does fill all 3 brackets and 0 if not found.
So attached is my actual spreadsheet, I used the formulas recommended here and it works.
ActualExample.xls
What would help me now is if on Sheet2 i can somehow group together same "Bracket 1 Unit Prices" and add together their Bracket 1 Unit Totals
Example
.97 - 139.68
.97 - 104.76
.97 - 139.68 = 384.12 (and maybe even list one upc of that group with the total)
1.99 - 15.92 = 15.92 ( in other instances there would be multiple 1.99 items just like above )
2.34 - 2.34 = 2.34 ( in other instances there would be multiple 2.34 items just like above )
prices are not always gonna be in order like they are right now, but will be all over.
Example:
.97
.97.
1.99
.97
1.99
2.34
.97
1.99
1.99
.97
2.34
is that possible?
Last edited by dynamoracing; 03-05-2013 at 02:05 PM.
This might work for you, click the 1 2 and 3 buttons on the left side of the sheet2.
I really dont understand how to do that or how to manipulate it. If you can maybe explain it to me.Originally Posted by Dunc3142
What Im really looking for is what i have in the spreadsheet attached, it just needs to be auto populated, I can even input all different prices manually if it can pull same prices into groups.
Calculations2.xls
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks