Excel Help Needed....

[bcbm]

I would like to use excel to keeping track of football teams. I would like to have 1 sheet with 4 colums. col 1 for names, col 2 for wins, col 3 for loss's, col 4 for ties. Then in a 2nd tab of the same workbook I would like to be able to enter the name of the team and have it pull the information for that team in. Is there any way to do that, if so can somebody point me in the right direction.

Brian

 

[SkipVought]

Check out either VLOOKUP or faq68-5829.

Alos your team selection on sheet2 could be a Data > Validation - List drop down selection, that would reference a TeamList.

May I further suggest using Named Ranges to aid in the understanding of your design. faq68-1331.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[bcbm]

Thanks, but 1 more question. How do I set the table array ? I looked through the Excel help file and I don't see it listed.

 

[SkipVought]

Nothing special.

One row or headings.
Contiguous rows & columns of data (no empty rows or columns within the table)

Check out VB Help on List & Tables and faq68-5184.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[bcbm]

Skip, Thanks for the help. Do you know of any place I could get examples. I seem to get things more when I can see them work. Even something simple would be great.

 

[SkipVought]

Post a sample of your table.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[bcbm]

Skip,

Thanks for the help, but I found a really good web site with alot of examples and I have the sheet working. It turns out the VLOOKUP worked best for me if I just had everything on 1 sheet instead of trying to use 2 sheets in the same work book. Here's the web site if anybody else wants to check it out.

http://www.contextures.com/tiptech.html

 

[SkipVought]

"It turns out the VLOOKUP worked best for me if I just had everything on 1 sheet instead of trying to use 2 sheets in the same work book."

Why is 2 sheets a problem? It has never hindered me using VLOOKUP, as I originally suggested. I almost ALWAYS have tables on a sheet other than the one I use my lookup function on.
[tt]
=VLOOKUP(A2,[v]Sheet1![/b]"A2:Z9999",2,false)
[/tt]



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[bcbm]

I'm not sure what the issue is yet. I'm just happy to have a simple copy working for now. The next step will be getting it to work on 2 sheets so it looks cleaner.

I do have 1 more question now. I have my sheet set up that after the game the winning team gets a new rating value, I now want to transfer that value back to the lookup table so that I can always get the latest ratings.

 

[SkipVought]

oops...
=VLOOKUP(A2,Sheet1!A2:Z9999,2,false)

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[bcbm]

OK, well I have this thing working pretty good. Any idea how I can update the rating field where the original rating came from. I tried to use the VLOOKUP and just grab from the cell that had the new rating, but excel tells me that I can't have a formula get a figure from another formula...any idea's ?????

 

[SkipVought]

A formula can get a value from another formula.

However, you cannot, under most circumstances, have a CIRCULAR REFERENCE.

Please explain what you are trying to do. Please be clear, concise and complete.



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[bcbm]

The CIRCULAR REFERENCE was my problem, but I did not know how to get around it

What I want to do is have 1 sheet with a colum for the list of football teams and a a colum with ratings that I assign. Then I want to have a 2nd sheet where I just enter the team name in colum A and have the rating pulled over to the sheet from the 1st sheet. On the 2nd sheet I want to be able enter if the team win or lose's, depending on winning or losing the rating will be adjusted. After being adjusted I want the 2nd sheet to update the rating on the 1st sheet so that when I pull the rating it is always the most current rating.

Sheet 1
-------
Name -- Rating
Chicago -- 1
Seattle -- 2
Dallas -- 3

Sheet 2
-------

Name ------ Rating ----- W / L ----- New Rating
-----------------------------------------------
Chicago ----- 1 ----- W -----
Dallas ----- 3 ----- L ----- 1
(Winning gives 2 point, losing takes 2 points)
( The New Rating colum should update the Rating colum on sheet 1)

If you need more info please let me know.

 

[SkipVought]

"...the rating will be adjusted"

THAT cannot be done with a FORMULA, else you will get a circular reference.

It can be done with a macro, but that will require VBA Code. How do you feel about coding a solution?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[bcbm]

Depending on how much I would be ok. How much coding are you talking about.

 

[SkipVought]

It would take using the Worksheet_Change event in the New Rating column. Then you'll have to locate the Team row in the other sheet and assign the new rating.

If you want to try, post your question in Forum707.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}