Results 1 to 15 of 15
Thread: Not sure.. (Excel 2002)

20031022, 13:24 #1
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 19 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

20031022, 14:13 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Not sure.. (Excel 2002)
Can you tell us what the relationship between color number and symbol is?

20031022, 14:18 #3
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!

20031022, 14:19 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20031022, 15:29 #5
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20031022, 16:16 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Not sure.. (Excel 2002)
See attached workbook. Not sure it is what you want...

20031022, 16:20 #7
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20031022, 17:43 #8
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20031022, 19:48 #9
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 HelpsGrüße

20031022, 19:51 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20031023, 13:44 #11
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!!!

20031023, 13:51 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20031023, 14:00 #13
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20031023, 15:40 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20031023, 18:08 #15
 Join Date
 Jan 2001
 Posts
 313
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Not sure.. (Excel 2002)
ahhhhh  it just clicked. Thank you again HansV  I really appreciated it.
Melanie