As planders suggested, it sounds like you are looking for LOOKUP (or possibly HLOOKUP or VLOOKUP). This will look for the value you specified in the reference cell within a list, and return a value from the adjacent cell on the lookup list. If the columns/rows on the lookup list are not side by side then VLOOKUP (columns) or HLOOKUP (rows) are what's needed.
Another way to accomplish this is with a coupling of the INDEX and MATCH functions. This is something needed for lists which are not ordered/alphabetized. It's a bit more complicated with this method, but much more flexible.
start by building a LOOKUP to get the data you want from the team spreadsheet. Use the formula builder to walk you through it.
When you are done with the LOOKUP, wrap it in an IF statement as shown earlier, where the LOOKUP is the positive return.
You need to do the IF statement for accuracy since Excel will return the closest available value in a LOOKUP (ie. if the lookup value doesn't exist, you'll still get a return)
You also need to make sure that the lookup table is sorted on the column that you are searching.
Hope that makes sense.
__________________
“Maybe some day somebody will call me "Sir" without adding "You're making a scene"!!”
-Homer Simpson
From the sounds of it, you need a database, not a spreadsheet. Excel does have some databse functionality, but you would be much better served using actual database software.
Excel is perfectly suited to this kind of thing. Depending on what else he wants to do, a database might be recommended. But this is no problem for excel and works well once set up.
__________________
“Maybe some day somebody will call me "Sir" without adding "You're making a scene"!!”
-Homer Simpson
Sorry, but this isn't what I am looking for. I am making a golf pool, so I have more than one name. On an excel sheet are the teams with the players listed on each person's team. I want to be able to specify the weekly PGA money list with the names of the players and their earnings so far this year and then i want excel to put a person's earnings next to their name (in the box to the right) every time their appears in the excel sheet.
yes the season has already started and tournaments are going every week now in golf and its soo hard to update each week. will be looking for a databse program.
Thanks, Rob, but that wont work for me. If woods is in first place then he will be in A1 and his money will be in B1. If woods moved into second place after a tournament, and lets say Mike Wier then went into first on the money list, then woods's name would be in A2 and his money in B2. If I used =$B$2 then it would be copying Mike Wier's money earned.
You can easily do a sort by name or earnings. Select both columns and then go to the "Data" menu. Choose "Sort". You can sort by column in ascending or decending. In other words, you can sort by player name in alphabetical order, or you can sort by earnings from highest to lowest or lowest to highest. As long as you have both columns highlighted then Excel is smart enough to move your absolute references with the sort.
I think you're making this waaay too hard. There's a million ways to do this if you plan it properly in the original setup. You're going to have to experiment a bit and read up on some of the Excel functions. A database program isn't going to be any easier if you've never used it before.
__________________
Real Macsters don't repair permissions.
Opinions are like toothbrushes. Everybody has one, so there's no need to share. - Red Green
FYI - I got a copy of the spreadsheet from Harrydude and it's all set up and working. The lookup is functioning well combined with an if statement to keep it accurate.
No need for a database
__________________
“Maybe some day somebody will call me "Sir" without adding "You're making a scene"!!”
-Homer Simpson
Linear Mode
