Excel Question

Discussion in 'OT Technology' started by Repentinus, Jul 1, 2007.

  1. Repentinus

    Repentinus New Member

    Joined:
    Aug 13, 2005
    Messages:
    586
    Likes Received:
    0
    Location:
    Lawton, OK
    Is there a way to make Excel recognize a number in a cell with text as a number? Therefore I can use it in an AutoSum?

    Basically I want to keep the number with the text because I use the same names frequently and want to keep the ability to use the autofill (not sure if that right but its when you type the first few letters it autofills out the rest of the word/words for you.)

    But I also would like to sum up the numerical values in those cells. Thanks.
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    You can either

    a) split the two values up into two separate columns and optionally combine them together into a calculated field into a 3rd column.
    b) extract the numeric information into a separate column and sum that.

    But an example of what you're trying to do is best because your question makes me believe you've come up with a pretty stupd solution to the problem.
     
  3. Sexual Vanilla

    Sexual Vanilla New Member

    Joined:
    May 23, 2005
    Messages:
    6,305
    Likes Received:
    0
    Location:
    South Carolina
    Split the cells as 5Gen said. The data was organized poorly from the start, and therefore you have to pay the consequences.
     
  4. Repentinus

    Repentinus New Member

    Joined:
    Aug 13, 2005
    Messages:
    586
    Likes Received:
    0
    Location:
    Lawton, OK
    I'm creating a cost list of items for a game I play, Everquest.

    Each item I use has a cost associated with it. Originally I was typing in the name of the item and the coast of it in the same cell and then adding the sums of the cost manually.

    {Suspension of Darkness} {1p1g4s4c} {Crude Shaped Potion Vial (1p)} {Sticklewort (1g4s)} {Madienhair x 4 (4c)}

    I then thought that it would be better to autosum the values so I put the cost in a separate column, removed the p g s and c, and autosummed them. That worked fine.

    {Suspension of darkness} {1144} {Crude Shaped Potion Vial} {1000} {Sticklewort} {140} {Madienhair x 4} {4}

    But then I got tired of typing looking up the cost of each item each time because originally it autofilled in with the name when it was in the same cell. So basically I would like to keep the ability to autofill in the cost while being able to autosum those costs to a cell.
     
  5. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    You can probably do this in excel. I know it's possible in Access. You'd basically link the cells. They have different data, but you establish a relationship between them. For example, product name and cost are both different, but related.
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    It sounds like you should create a table of all of the items, and then use drop down lists to grab the item. Then use a vlookup of the item and find its cost to put in the 2nd column. You need to use two columns (or 5 if you want to maintain your platinum, gold, silver and copper identifiers - I didn't think that 1p=10g=100s=1000c)
     

Share This Page