Hi Guys, i'm trying to find a pretty long winded formula which hopefully will save me a few days work...
I will simplify everything as much as I can then show an example...
I have the following fields.
<tbody>
</tbody>
The bold fields are the data which i am missing on the spreadsheet. This is heald in another sheet, which is set up in a different format. Where the above table only shows variables for the products in question. This sheet i am working off has them layed out such as... (V1N = Variable 1 name, V1v = Variable 1 value)
V1N, V1V, V2N, V2V, V3N, V3V, V4N,V4V......... V15N, V15V...Variable name always contains a value, So V1 Name will say "Length" all the way down, V2 would be height all the way down.. When required to add a variable it is added next to the appropriate "value" collumn.
I am needing to do the following...
I need Sheet 1 to look at value "Sku" as a reference. Then if VAR1Name contains "colour".. Then look in sheet 2 for the same sku and find colour in there (say it found colour in cell P2. I want it then to select P3, and copy it into book 1 (under the collumn Var1value)
Then want it to procede to the next collumn (which would be var2Name) and do the same, to bring back a result for "Size" from sheet 2)
The only possible way i can think of this is with a vlookup and an if statement. the if statement assigns a number to every value.. then the vlookup looks for that value in the other sheet? but not show how it would work of it theres a less long winded way??
I will simplify everything as much as I can then show an example...
I have the following fields.
Type | Sku | Var1Name | Var1Value | Var2name | Var2Value | Var3name | Var3Value |
Variable | Tshirt1 | Colour | White,Black | Size | Medium,Large | ||
variation | TshirtWhiteM | Colour | White | Size | Medium | ||
variation | TshirtWhiteL | Colour | White | Size | Large | ||
variation | TshirtBlackM | Colour | Black | Size | Medium | ||
variation | TshirtBlackL | Colour | Black | Size | Large | ||
Variable | Jumper | Size | Small, Medium, Large | ||||
variation | JumperS | Size | Small | ||||
variation | JumperM | Size | Medium | ||||
variation | JumperL | Size | Large |
<tbody>
</tbody>
The bold fields are the data which i am missing on the spreadsheet. This is heald in another sheet, which is set up in a different format. Where the above table only shows variables for the products in question. This sheet i am working off has them layed out such as... (V1N = Variable 1 name, V1v = Variable 1 value)
V1N, V1V, V2N, V2V, V3N, V3V, V4N,V4V......... V15N, V15V...Variable name always contains a value, So V1 Name will say "Length" all the way down, V2 would be height all the way down.. When required to add a variable it is added next to the appropriate "value" collumn.
I am needing to do the following...
I need Sheet 1 to look at value "Sku" as a reference. Then if VAR1Name contains "colour".. Then look in sheet 2 for the same sku and find colour in there (say it found colour in cell P2. I want it then to select P3, and copy it into book 1 (under the collumn Var1value)
Then want it to procede to the next collumn (which would be var2Name) and do the same, to bring back a result for "Size" from sheet 2)
The only possible way i can think of this is with a vlookup and an if statement. the if statement assigns a number to every value.. then the vlookup looks for that value in the other sheet? but not show how it would work of it theres a less long winded way??