As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

Excel help

NickleNickle Registered User regular
edited October 2007 in Games and Technology
Just a quick question, I'm hoping I can get help from someone out there. I'm not even sure on the terminology I'd use, which makes a search useless to me, so I'll describe my situation.

I work for a company that produces souvenir programs for sporting events, and I keep an excel worksheet of all of the events, with a column for participating teams, and a column for the contact person. Since we have a lot of teams that are competing in more than one event, I often have to copy the contact info for a school over and over, for each event.

Is there a formula or tool I can use to autofill the contact column? Say I've already entered a contact for Johnson High School in a Volleyball program, and they're competing next week in a Football game, can I have excel pull the value from the contact column from Volleyball, and insert it in the column for Football?

Basically, I'm looking for a 'if C###=C### then D###=D###', but I don't know excel syntax or features very well.

Any help would be great, thanks.

Xbox/PSN/NNID/Steam: NickleDL | 3DS: 0731-4750-6906
Nickle on

Posts

  • Jam WarriorJam Warrior Registered User regular
    edited October 2007
    Sounds like you need the formula VLOOKUP which, 'Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array'.

    Now you know the name then help files should contain all you need. just don't forget to copy/paste special/values after you're done if you've got lots of them or the sheet will recalculate every time you make a change.

    Jam Warrior on
    MhCw7nZ.gif
  • NickleNickle Registered User regular
    edited October 2007
    Sounds like you need the formula VLOOKUP which, 'Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array'.

    Now you know the name then help files should contain all you need. just don't forget to copy/paste special/values after you're done if you've got lots of them or the sheet will recalculate every time you make a change.

    Thanks, I'll look into it.

    I'm not sure the second part applies, because if I update the contact info, I'd rather it change in every row that includes it, anyway.

    Nickle on
    Xbox/PSN/NNID/Steam: NickleDL | 3DS: 0731-4750-6906
  • Jam WarriorJam Warrior Registered User regular
    edited October 2007
    That's fine, it's just a habit I get into because I work with sheets with thousands of rows and the whole thing can slow to a crawl if you're not careful.

    Jam Warrior on
    MhCw7nZ.gif
  • NickleNickle Registered User regular
    edited October 2007
    Is there anyway to do this automatically, or do I have to enter the forumla for each row I add?

    For instance, I've entered
    Basketball-Johnson-Mr. Bob
    Basketball-Washington-John Doe

    When I enter
    Volleyball-Johnson-
    Volleyball-Washington-

    is there a way to have excel automatically pull the third column of info, based on the second column's values? Looking over VLOOKUP, it seems it can only work with the first column, and it seems that I'd have to enter the formula individually for each school as I enter it. Basically, I'm just looking for a method to link column D and column H, so that when a value is entered in Column D, excel searches the sheet for a similar value in Column D, and auto fills the value from Column H.

    Basically, I was thinking some kind of formula similar to 'For D(x)=D(y), H(x)=H(y)' where x is the row number to fill, and y is the row number found by excel, through searching column D for matching values. I'm not sure if excel can do that. I've already entered all of the values for column D, and now I'd need excel to match the values in column D to previously entered values for Column H in the same row.

    It is possible I'm misunderstanding VLOOKUP, though, sorry if I'm slow, I'm still waking up.

    Nickle on
    Xbox/PSN/NNID/Steam: NickleDL | 3DS: 0731-4750-6906
  • Mr_GrinchMr_Grinch Registered User regular
    edited October 2007
    Could you fire over a little example spreadsheet to mr.grinchx@gmail.com if you don't get it sorted? I'm bored in work and will give it a shot for you :)

    Mr_Grinch on
    Steam: Sir_Grinch
    PSN: SirGrinchX
    Oculus Rift: Sir_Grinch
  • NickleNickle Registered User regular
    edited October 2007
    Thinking about it more, unless there is a function that does it built into excel, an example of what I'd be looking for is a formula to run over the whole sheet where basically for each row, excel searches for a simlar value in column D in a preceding row. For instance if the row is 300, it will search for similar values in column D in rows < 300 and pull the value from the preceding row, column H into row 300, column H).

    As of now, I just have to keep cross referencing my address book, copying the school name into it, copying the contact back into excel. I've actually gotten pretty quick with it, but it just seems like there should be an easier way, considering I've already entered the contact for that school many times over.

    Nickle on
    Xbox/PSN/NNID/Steam: NickleDL | 3DS: 0731-4750-6906
  • OrogogusOrogogus San DiegoRegistered User regular
    edited October 2007
    Nickle wrote: »
    Looking over VLOOKUP, it seems it can only work with the first column, and it seems that I'd have to enter the formula individually for each school as I enter it.

    I don't think this is a problem as long as you don't give VLOOKUP the first column to work with. That is, use a range that starts from the second column.

    Orogogus on
  • redfenixredfenix Aka'd as rfix Registered User regular
    edited October 2007
    Maybe i'm not entirely understanding the issue, but would it be possible to set up the oft-used data as some kind of template, or copy just the reusable stuff to a blank sheet/file to reuse? Im sure the automated approach is nice, but it may be a bit heavy-handed.

    redfenix on
  • mfc144mfc144 Registered User regular
    edited October 2007
    I think this does what you're asking.
    excel file

    Basically the first worksheet is the list of your events and participants with a 3rd column for contact name. This column is looked up from a table on the second worksheet.
    The second worksheet (Contacts), has a table with participant rows and event type columns, with contact names as the table cell data.
    When you enter an event and location into a row on the first worksheet, the contact name will be filled in. (if that contact exists in that table.

    If you add more columns or rows to the contact table (more events or participants), you will need to adjust the ranges on the contact name function on the first worksheet. I don't know exactly what I'm doing with excel, so maybe there is a way to make that auto adjust, but I don't know it.


    *Edit, accidentally uploaded a workbook that wasn't working. If you have already downloaded it, try the link again for a working version.

    mfc144 on
  • NickleNickle Registered User regular
    edited October 2007
    Thanks everyone for your help. Grinch has sent me a formula to work with, but I'm pretty stupid with excel, so here's where I am now.

    Right now, I am on row 456 of the sheet, and in column H of that row, I have the formula:
    =LOOKUP(D456,$D$2:D455,$H$2:H455)
    

    (Row 1 is a header row)

    the value in D456 is 'Antigo', but I keep getting a #N/A answer in Column H, even though 'Antigo' is entered in Column D of at least a few rows above.

    Any ideas?

    And I do want to take the time to mention how awesome you guys are, for helping me out for no good reason.

    e: mfc, that's a great example, and I think it might apply to some of the other sheets I work with. If anything, it's a good chance for me to learn more about excel, so thanks for that. I think it's a little more complicated than I would need in this instance, but I can see that I could adapt it to work. I was just going to ask about making a seperate sheet just for school name/contact to reference to, so if I go that route I'll be using your example.

    Nickle on
    Xbox/PSN/NNID/Steam: NickleDL | 3DS: 0731-4750-6906
  • redfenixredfenix Aka'd as rfix Registered User regular
    edited October 2007
    maybe it's some invisible formatting thing, or a difference in font, size, etc?

    redfenix on
  • Mr_GrinchMr_Grinch Registered User regular
    edited October 2007
    Did that next one help anymore? the one using vlookup?

    Mr_Grinch on
    Steam: Sir_Grinch
    PSN: SirGrinchX
    Oculus Rift: Sir_Grinch
  • NickleNickle Registered User regular
    edited October 2007
    Mr_Grinch wrote: »
    Did that next one help anymore? the one using vlookup?

    Yes, that works perfectly, you are the man.

    right now, I have (in row 456)
    =IF(D456="","",VLOOKUP(D456,$D$2:H455,5,FALSE))
    

    Could you explain what the 5 is for, near the end? I couldn't get it to work at first, because I figured the 5 was a variable. I understand the rest of the formula, though.

    In any case, you are like a god to me, right now. :) (JamWarrior too, who basically said the same thing, but underestimated my stupidity)

    Thanks again to everyone else who offered their assistance. Seriously, you guys rock.

    Nickle on
    Xbox/PSN/NNID/Steam: NickleDL | 3DS: 0731-4750-6906
  • Jam WarriorJam Warrior Registered User regular
    edited October 2007
    Nickle wrote: »
    Mr_Grinch wrote: »
    Did that next one help anymore? the one using vlookup?

    Yes, that works perfectly, you are the man.

    right now, I have (in row 456)
    =IF(D456="","",VLOOKUP(D456,$D$2:H455,5,FALSE))
    

    Could you explain what the 5 is for, near the end? I couldn't get it to work at first, because I figured the 5 was a variable. I understand the rest of the formula, though.

    In any case, you are like a god to me, right now. :) (JamWarrior too, who basically said the same thing, but underestimated my stupidity)

    Thanks again to everyone else who offered their assistance. Seriously, you guys rock.

    vlookup syntax runs thus: vlookup([cell you are looking up from],[range you want to look up in],[column you are looking up],[If I can't get an exact match I will guess at it True/False])

    i.e. the 5 is because you are looking up the value in the 5th column of your lookup table.

    Jam Warrior on
    MhCw7nZ.gif
  • NickleNickle Registered User regular
    edited October 2007
    Nickle wrote: »
    Mr_Grinch wrote: »
    Did that next one help anymore? the one using vlookup?

    Yes, that works perfectly, you are the man.

    right now, I have (in row 456)
    =IF(D456="","",VLOOKUP(D456,$D$2:H455,5,FALSE))
    

    Could you explain what the 5 is for, near the end? I couldn't get it to work at first, because I figured the 5 was a variable. I understand the rest of the formula, though.

    In any case, you are like a god to me, right now. :) (JamWarrior too, who basically said the same thing, but underestimated my stupidity)

    Thanks again to everyone else who offered their assistance. Seriously, you guys rock.

    vlookup syntax runs thus: vlookup([cell you are looking up from],[range you want to look up in],[column you are looking up],[If I can't get an exact match I will guess at it True/False])

    i.e. the 5 is because you are looking up the value in the 5th column of your lookup table.

    Thanks for the info. Now that I've gotten into it a bit, I won't be able to get the hooks out. For a more permanent solution, I think I might tool around a bit with mfc's example.

    Learning a new syntax or new programming language always interests me. My OCD kicks in, and now I'm more interested in the code than the problem I had. :) Now I'm going to spend all night reading up on excel, even though I probably won't even have a real use for most of what I learn.

    It's a disease, I tell you.

    Nickle on
    Xbox/PSN/NNID/Steam: NickleDL | 3DS: 0731-4750-6906
Sign In or Register to comment.