Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pro Football formula automation
I posted a similar question on another board, haven't had luck yet.
Will try to explain it better here. In column B, I have opponents for teams in column A. I have empty cells between each team in column A also. Ex: A2:A17 is Arizona, B2:B17 is San Fransisco, Seattle, Baltimore, etc. (Opponents of Arizona) Then I skip 2 lines and list Atlanta in A20:A37 and list their opponents in B20:B37, and I do this for all 32 teams. The last entry ends on row 638. I have Named Ranges for each team, but the names differ from the team names. Arizona's Named Range is "Ari_Avg", Atlanta's is "Atl_Avg", San Fransisco's is SF_Avg etc. What I need to do (in VBA I suppose), is write a formula in column G that looks in column B, and if B2 text is San Fransisco, then write the formula "=AVERAGE(SF_Avg)" I have searched Help and Newsgroups for an answer, and have tried different methods, but can't get my code to work. I tried using 32 if's (one for each team) but keep getting errors, like If without block If. Would it be easier to name my Named Ranges to equal the text in B2:B638, then use Offset, like another guy tried to help me with? Shoot, I couldn't even make that work. I think I need something like: If c.Value(B2:B638) = San Fransisco Then (G2:G638).Formula = "AVERAGE(SF_Avg)" Hope someone can help. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pro Football formula automation
Hi Tim -
If you'd be willing to rename your ranges, a very simple formula will work without any fussing with VBA. The formula as entered in G1 would be: =Average(Indirect(B1)) To make this work, you'll have to rename all of your XXX_Avg ranges to the team's city and use that name (exactly) in the opponents column. For example, rename the SF_Avg range to "SanFrancisco" and use that exact name in the opponents column. If you stick with your original approach, we have two choices, both of which seem to be more work than the approach described above: 1. Build a cross-reference table somewhere in your workbook that cross-references the value in the oppenents column with the XXX_Avg range name. Then, add the VLookup function to the formula in column G: =Average(Indirect(Vlookup(B1,CrossReferenceTable,2 ,False))) 2. Build a VBA procedure. --- Jay "Tim" wrote: I posted a similar question on another board, haven't had luck yet. Will try to explain it better here. In column B, I have opponents for teams in column A. I have empty cells between each team in column A also. Ex: A2:A17 is Arizona, B2:B17 is San Fransisco, Seattle, Baltimore, etc. (Opponents of Arizona) Then I skip 2 lines and list Atlanta in A20:A37 and list their opponents in B20:B37, and I do this for all 32 teams. The last entry ends on row 638. I have Named Ranges for each team, but the names differ from the team names. Arizona's Named Range is "Ari_Avg", Atlanta's is "Atl_Avg", San Fransisco's is SF_Avg etc. What I need to do (in VBA I suppose), is write a formula in column G that looks in column B, and if B2 text is San Fransisco, then write the formula "=AVERAGE(SF_Avg)" I have searched Help and Newsgroups for an answer, and have tried different methods, but can't get my code to work. I tried using 32 if's (one for each team) but keep getting errors, like If without block If. Would it be easier to name my Named Ranges to equal the text in B2:B638, then use Offset, like another guy tried to help me with? Shoot, I couldn't even make that work. I think I need something like: If c.Value(B2:B638) = San Fransisco Then (G2:G638).Formula = "AVERAGE(SF_Avg)" Hope someone can help. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pro Football formula automation
Thanks Jay. I was going to try something like that earlier, but my
Teams often were two words, and I couldn't name a range like that. I believe I'll make them one word and try that. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for evaluating predictions - Fantasy Football League | Excel Discussion (Misc queries) | |||
Formula for Football (Soccer) Prediction League | Excel Discussion (Misc queries) | |||
Supressing the ctrl-c and other keys during word automation in automation | Excel Programming | |||
Fantasy Football Datbase or Formula | Excel Discussion (Misc queries) | |||
Football Predictions Spreadsheet formula problem | Excel Programming |