Pro Football formula automation

[Tim]

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.

 

[Jay]

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.

 

[Tim]

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.