Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    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 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. #2
    Plutonium Lounger
    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?

  3. #3
    3 Star Lounger
    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!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    3 Star Lounger
    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

  6. #6
    Plutonium Lounger
    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...

  7. #7
    3 Star Lounger
    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

  8. #8
    3 Star Lounger
    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.

  9. #9
    Platinum Lounger
    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 Helps
    Gre

  10. #10
    Plutonium Lounger
    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.

  11. #11
    3 Star Lounger
    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!!!

  12. #12
    Plutonium Lounger
    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.

  13. #13
    3 Star Lounger
    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.

  14. #14
    Plutonium Lounger
    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.

  15. #15
    3 Star Lounger
    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

Posting Permissions

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