Dec 12 2018 11:00 AM
Dec 12 2018 11:24 AM
Do you have a list of all of the codes and color names that you reference then?
Is the color code in cell by itself?
Dec 12 2018 11:28 AM
Dec 12 2018 01:38 PM
OK, This should be fairly straightforward, are you able to share a partial sample? (I'll walk you through this either way but as you said your Excel knowledge is pretty basic, would be easier if you could share a sample)
Dec 12 2018 01:44 PM
I attached a sample. I hope it helps.
Dec 12 2018 01:56 PM
OK, I am going to assume your other sheet has 2 columns one with the paint code and one with the name.
In the cell to the right of 06X1 on the sheet you posted (I am assuming 06X1 is in F1, so the cell to the right would be G1) enter =Vlookup(F1, Then select the columns on the sheet with the paint codes and paint names, then ,2,false) then hit enter, you should be able to ten copy the formula down column G to populate everything on the sheet.
The formula should read: =VLOOKUP(F1,[Book2]Sheet1!$A:$B,2,FALSE)
where [Book2] is the name of the workbook and Sheet1 is the name of the sheet in that workbook with all of the paint colors codes
Again, I am assuming the workbook containing the codes/color names has color code in Column A and Color name in column B, if that's not the case let me know and we can revise this.
Dec 13 2018 06:33 AM
So, the color codes are in the same workbook, just sheet 2 named Color. I don't know if that changes the formula at all. I obviously did something wrong as I am getting a #N/A error. Mine ended up looking like this. I apologize if I butchered it at all...
=VLOOKUP(F1,[KeyTags.xlsx]Color!$A66:$B66,2,FALSE)
Dec 13 2018 07:03 AM
SolutionOK, So first off my apologies as I didn't realize I could actually download what you posted (Thought it was a screenshot, just joined the message board yesterday!), I am an idiot and probably shouldn't have been trying to help anyone yesterday. Very sorry.
Use this in G1, I tested it and it seems to work for me:
=VLOOKUP(IF(LEN(F1)>3,RIGHT(F1,3),(IF(LEN(F1)=2,"0"&F1,F1))),Color!A:B,2,FALSE)
Had to get a little more complicated with the formula as it looks like sometime you get the paint codes as 4 digit codes with an extra leading 0 and sometimes a 2 digit code with a 0 dropped off the front (In relation to the all the codes being 3 digits codes on your sheet "Color"). This is essentially the same formula I sent before but I added: If the paint code we want to look up is 4 digits, remove the first digit, If it's 2 digits add a "0" to the front, thereby always ending up with a 3 digit code to lookup on your other sheet.
Dec 13 2018 07:07 AM
OMG, it worked. You're brillant. Thank you so much!
Dec 13 2018 07:38 AM
Dec 13 2018 07:03 AM
SolutionOK, So first off my apologies as I didn't realize I could actually download what you posted (Thought it was a screenshot, just joined the message board yesterday!), I am an idiot and probably shouldn't have been trying to help anyone yesterday. Very sorry.
Use this in G1, I tested it and it seems to work for me:
=VLOOKUP(IF(LEN(F1)>3,RIGHT(F1,3),(IF(LEN(F1)=2,"0"&F1,F1))),Color!A:B,2,FALSE)
Had to get a little more complicated with the formula as it looks like sometime you get the paint codes as 4 digit codes with an extra leading 0 and sometimes a 2 digit code with a 0 dropped off the front (In relation to the all the codes being 3 digits codes on your sheet "Color"). This is essentially the same formula I sent before but I added: If the paint code we want to look up is 4 digits, remove the first digit, If it's 2 digits add a "0" to the front, thereby always ending up with a 3 digit code to lookup on your other sheet.