I've never done this before, so please be gentle with me.
I have price info in a grid, and I need a 40 line invoice where I can just
type in the SKU which is essentially a combination of the data name from the
COLOR and STYLE number from the grid as shown as an example

STYLES - 101 102 103 104 104 106 107 108
COLORS BLUE .50 .75 1.10 .85 .60 .20 .42 .37
RED .50 .75 1.10 N/A .60 .20 .42.
N/A
GREEN .65 .85 N/A .95 .75 .30 N/A
..42
YELL .40 .65 N/A .75 .55 .30 .37
N/A

SKU example: BLUE102 = .75

How do I set this up?
The entire data set is 5000 items if I list each SKU as a combined
COLOR/STYLE with it's coresponding value beside it for a vlookup search.

any suggestions please?

ORA

each style has one of four color scheme?
you want to print out an invoice for the customer that buys or orders say a
blue102?

first i think it would easier to use the 102blue rather than blue102;
further since the first letters of the colors are unique you can use 102b.
so COL 1 is STYLE #, COL 2 is Color (b,r,g,y)
COL3 then would convert the color code to a number
if(col2="b",1,if(col2="r",2, if(col2="g",3, if(col2="y",4,0)))
then your loookup would be something like this:
hlookup(a1,styles,c1) where styles is the name of the lookup table

so using your example style 102 is the second column, and blue is the first
row and is .75 and 107y is .37 and so forth.

hope it helps


if you know lookup tables, then you could use this something like this:


"excelotard" wrote:

I've never done this before, so please be gentle with me.
I have price info in a grid, and I need a 40 line invoice where I can just
type in the SKU which is essentially a combination of the data name from the
COLOR and STYLE number from the grid as shown as an example

STYLES - 101 102 103 104 104 106 107 108
COLORS BLUE .50 .75 1.10 .85 .60 .20 .42 .37
RED .50 .75 1.10 N/A .60 .20 .42.
N/A
GREEN .65 .85 N/A .95 .75 .30 N/A
.42
YELL .40 .65 N/A .75 .55 .30 .37
N/A

SKU example: BLUE102 = .75

How do I set this up?
The entire data set is 5000 items if I list each SKU as a combined
COLOR/STYLE with it's coresponding value beside it for a vlookup search.

any suggestions please?

ORA

Thanks for the help so far.
Actually each Color comes in up to 40 different styles, and there are 50+
colors, So it isn't really true that the first letter of the colors are
unique, as there are for example Blue and Black.

Te real goal here is how do I cross locate the price? Kind of like how you
used to find the mileage on an atlas as the intereseection of two points.

Ouch

"reno" wrote:

each style has one of four color scheme?
you want to print out an invoice for the customer that buys or orders say a
blue102?

first i think it would easier to use the 102blue rather than blue102;
further since the first letters of the colors are unique you can use 102b.
so COL 1 is STYLE #, COL 2 is Color (b,r,g,y)
COL3 then would convert the color code to a number
if(col2="b",1,if(col2="r",2, if(col2="g",3, if(col2="y",4,0)))
then your loookup would be something like this:
hlookup(a1,styles,c1) where styles is the name of the lookup table

so using your example style 102 is the second column, and blue is the first
row and is .75 and 107y is .37 and so forth.

hope it helps


if you know lookup tables, then you could use this something like this:


"excelotard" wrote:

I've never done this before, so please be gentle with me.
I have price info in a grid, and I need a 40 line invoice where I can just
type in the SKU which is essentially a combination of the data name from the
COLOR and STYLE number from the grid as shown as an example

STYLES - 101 102 103 104 104 106 107 108
COLORS BLUE .50 .75 1.10 .85 .60 .20 .42 .37
RED .50 .75 1.10 N/A .60 .20 .42.
N/A
GREEN .65 .85 N/A .95 .75 .30 N/A
.42
YELL .40 .65 N/A .75 .55 .30 .37
N/A

SKU example: BLUE102 = .75

How do I set this up?
The entire data set is 5000 items if I list each SKU as a combined
COLOR/STYLE with it's coresponding value beside it for a vlookup search.

any suggestions please?

ORA

Hi, I'm not an expert but assuming all your style number are 3 digits
long then you may try this:

first start by creating a table with your style number, in one column
type your first style number, in the second column type a corresponding
number starting with 2 like this:
col(1)-col(2)
101 - 2
102 - 3
103 - 4
104 - 5

...etc....make sure you enter your style number as text by placing an
apostrophy in front, then name your table....lets say "data"
You can also name your price grid.....lets say "price"

Now assuming your sku is in A1 then try this formula

=VLOOKUP(LEFT(A1,LEN(A1)-3),price,VLOOKUP(RIGHT(A1,3),data,2,0),0)


Hope this helps!

JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=472793

Try this:

Assume that the header of your list of styles are B1:I1, and the colums for
your colours are from A2:A5, and the data range for your price gird is B2:I5

=index(B2:I5,match("Blue",A2:A5),match(102,B1:I1))


"excelotard" wrote:

I've never done this before, so please be gentle with me.
I have price info in a grid, and I need a 40 line invoice where I can just
type in the SKU which is essentially a combination of the data name from the
COLOR and STYLE number from the grid as shown as an example

STYLES - 101 102 103 104 104 106 107 108
COLORS BLUE .50 .75 1.10 .85 .60 .20 .42 .37
RED .50 .75 1.10 N/A .60 .20 .42.
N/A
GREEN .65 .85 N/A .95 .75 .30 N/A
.42
YELL .40 .65 N/A .75 .55 .30 .37
N/A

SKU example: BLUE102 = .75

How do I set this up?
The entire data set is 5000 items if I list each SKU as a combined
COLOR/STYLE with it's coresponding value beside it for a vlookup search.

any suggestions please?

ORA