Wednesday, September 19, 2012

yogi_Compute Wins By Poolie Teams -- NFL Pool by Fantasy Football

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #762   Sep 19, 2012

user Football Fantasy said: (!category-topic/docs/spreadsheets/Dq4qHMOSANo)
=importhtlm table to use as data then reference that data
Ive created a spread sheet that uses teams winning records to calculate a small football pool between some friends. 
I've got the =importHTML(""; "table";)
since the winning record has its own cell, i can easily add these cells up. 
tricky part is, these teams move due to ranking, so I was curious if there is a way to create a function to search for the team, then use the cell beside it for the data?

Sheet "Score tally"
B3 (green bay), B4 (Tennessee), B5 (Cincinnati), B6 (Cleavland) 
D3 (Baltimore), D4 (San Diego), D5 (Detroit), D6 (Miami)
F3 (new england), F4 (Dallas), F5 (Kansas City), F6 (Minnesota)
H3 (Pittsburgh), H4 (Philadelphia), H5 (New york Jets), H6 (St. Louis)
J3 (Atlanta), J4 (New york Giants), J5 (Carolina), J6 (Arizona) 
L3 (San Fransisco), L4 (Denver), L5 (Jacksonville), L6 (Indianapolis) 
N3 (Houston), N4 (Seattle), N5 (Buffalo) , N6 (Tampa bay)
P3 (Chicago), P4 (New Orleans), P5 (Oakland), P6 (Washington)
All refer to "sheet2" for the teams win column. which is column B.

the cells on the first sheet "score tally", this is where I will need the function to find the team on sheet2 in column "A". then use the winning record in the same row, but in column "B". 
Once I get that number in column b, i want to show it on "score tally", in the cell i searched it for.
Once i get that number in that cell. I am happy.

following is a solution to the problem ... in this solution I have used left part of the Team's name in the sheet Score Tally to enable matching with the part of the Team's name in Sheet2