functiongoogle-sheetsinputoutputsku

Google Sheets SKU automated data

I'm looking to create a CSV that automates product colour and size variables (Columns I and K, highlighted yellow and blue) based on the value I put in the SKU (Column N, highlighted green).

I would like to create a SKU (Column N) that looks similar to 1-5 which would have the output of Blue (Column I) and 5 (Column K).

Another example of this could be 2-11 which would have an output of Red (Column I) and 11 (Column K).

The aim of this is to automate the process by assigning each colour and shoe size with it's own value to save the client as much time as possible.

Any help with this would be greatly appreciated.

Diagram

Link to CSV https://docs.google.com/spreadsheets/d/1lc8XcHDMyMFGAkegTRrBLLTlQN8oNVbU430vT0aeP4Y/edit?usp=sharing

Solution

  • Copy of product-list

    I1: =arrayformula(if(A1:A="Handle","Option1 Value",IF(LEFT(N1:N)="1","Blue",IF(LEFT(N1:N)="2","Red","Green"))))

    K1: =arrayformula(if(A1:A="Handle","Option2 Value",IF(LEN(MID(N1:N,3,3))>2,LEFT(MID(N1:N,3,3),2)&"."&RIGHT(MID(N1:N,3,3)),LEFT(MID(N1:N,3,3),2))))