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.
Posts
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.
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.
PSN: SirGrinchX
Oculus Rift: Sir_Grinch
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.
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.
SE++ Map Steam
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.
Right now, I am on row 456 of the sheet, and in column H of that row, I have the formula:
(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.
SE++ Map Steam
PSN: SirGrinchX
Oculus Rift: Sir_Grinch
Yes, that works perfectly, you are the man.
right now, I have (in row 456)
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.