1. Not sure.. (Excel 2002)

I need help and I am not even sure exactly where to begin. I am working on converting the results of pattern conversion software into Excel. I have a color, the manufacturers color number, and I need to convert that number into a usable symbol. There are 66 colors, I am using most of the alphabet in upper and lower case (with the exception of i,I and 0 (zero) and numbers 1-9 and finishing with symbols !@#\$%^. This will give me 66. I can copy and paste the print out from the software into excel but then I get stuck. I do not want to have to manually go through every pattern to change the color/number to one of my symbols.

any help would be greatly appreciated.
Thanks
Melanie

2. Re: Not sure.. (Excel 2002)

Can you tell us what the relationship between color number and symbol is?

3. Re: Not sure.. (Excel 2002)

Certainly - the symbol is printed on the pattern which the user looks at and follows to create the design. One symbol takes up much less space than the yarn companies 3 character number. Since I have so many patterns, I am going to keep the instructions on a separate spreadsheet and also list the exact number of skeins/packs per yarn with each pattern.

Does this help?? Thank you!

4. Re: Not sure.. (Excel 2002)

If I understand:
You can put your conversions into a 66 row x 2 column table and then use VLOOKUP to take the one value and obtain the second "translation" to your symbol.

Steve

5. Re: Not sure.. (Excel 2002)

OK - I have never really used vlookup - this is what I tried =VLOOKUP(1,B3:C3,a) returned a name? error. The following is part of the table. I was thinking that whenever I created the pattern I would just copy and paste the pattern info from the software into excel and whever it said 1 white, it would automatically change to A white, and so on down the list for whatever colors are used in the pattern.
Number My symbol Color
1 A White
12 B Black
111 C Natural
230 D Yellow
244 E Coraline
245 F Orange
246 G Sea Coral

thanks

6. Re: Not sure.. (Excel 2002)

See attached workbook. Not sure it is what you want...

7. Re: Not sure.. (Excel 2002)

Yes - that is great! I will work on the rest of it (if I can figure it out!!!)

thank you

8. Re: Not sure.. (Excel 2002)

Ok - I am lost now - of course it doesn't take much... I am not sure how I am going to copy and paste. If I am pasting the yarn # into the spreadsheet, which column am I pasting in?
And again, many thanks.

9. Re: Not sure.. (Excel 2002)

What you need to do is <UL><LI>Convert Columns A to C into a complete list of all 66 of your Yarn #s. <LI>Then, you need to put the worksheet with all of your Order details into the same workbook. <LI>Insert a column immediately to the right of that with your Yarn # Orders and copy <!profile=HansV>HansV<!/profile>'s formula all the way down. <LI>That will give you your symbol conversion.[/list]If we haven't quite grasped how you are organising yourself, and the workbook is less that 100K in size, please feel free to include as an attachment in your reply - removing any confidential details beforehand.

Hope This Helps

10. Re: Not sure.. (Excel 2002)

Whatever column you like, basically. The first argument of the VLOOKUP function is the cell address of the value you want to look up. In the example I posted, I put the yarn numbers in column F, so the first argument of the VLOOKUP functions refer to column F. If you paste a yarn number in Z37, use Z37 as first argument.

11. Re: Not sure.. (Excel 2002)

Ok - I tried and I am not getting it - I copied and pasted, I tried dragging the formulas down the columns - I cannot figure out what I am doing wrong. Could be old age (not really) could be that we are having snow flurries here in Pennsylvania....not sure but I am usually able to figure this out.
See attached - thank you!
HELP!!!

12. Re: Not sure.. (Excel 2002)

In the example I posted, I named the lookup range ColorTable; since I used the limited set you posted, it is only the range A2:C8. You have expanded the lookup range to A2:C67, but ColorTable hasn't grown with it. There are ways to make such a named range dynamic, but in this case it is sufficient to redefine ColorTable to refer to A2:C67 (in Insert | Name | Define...)

See attached modified workbook.

13. Re: Not sure.. (Excel 2002)

Thank you it looks great - but I am still not seeing how you did it. Where are you naming the lookup range? I keep looking at both spreadsheets and they look the same - I am not making the connection. I am so sorry to be a pain but I know I should be getting this and I am not..

I really appreciate your time and effort.

14. Re: Not sure.. (Excel 2002)

Select Insert | Name | Define. You will see a name ColorTable in the list. If you click on it, you will see the range this name refers to in the "Refers to:" box. In the first version, it was =Sheet1!\$A\$2:\$C\$8, and in the second version it is =Sheet1!\$A\$2:\$C\$67.

If you prefer, you can replace ColorTable with \$A\$2:\$C\$67 in the formulas in column G and H. The reference must be absolute (witness the \$ signs) in order to be able to copy the formulas down.

15. Re: Not sure.. (Excel 2002)

ahhhhh - it just clicked. Thank you again HansV - I really appreciated it.

Melanie

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•