Formula needed

  • Thread starter Thread starter Fu2M4n
  • Start date Start date
F

Fu2M4n

OK well here goes.

Working on Gameday sheet right now.
Check out 'Screenshots Here' (http://fu2m4n22.tripod.com)
Now A2 and A3 are always gonna change. So that means the Whole Formula
is gonna have to be generic.

B2 will either be 4.5 or 5.5 all the time.

Now im Trying to fill Cell "E2" on The GameDay Workbook.
I Highlighted "VS Offense" to make it eaiser to find on the other
screenshots.

So the formula should lookup this workbook somehow. This is where it
has to grab all the data.
[NHL.xls]" & A2 & "!N4:N9"

In other words it has to lookup whatever team's worksheet that i mark
in A2.
(All of the worksheet names are identical to the names im gonna type in
here.)

Once it has done that then it has to figure out Which "Offensive Class"
the team in A3 is in.

If you look at screenshot 2 youll see that Atlanta is in "Class A"

Now we can move on to the final step which would be to Grab the Data on
A2's sheet while looking at "Class A" Row.(Range N4:N9)

Whatever team i mark down in A2 is always gonna be the "Away" team for
that game.
So that means we want the Away Data. Either gonna be Column Q or W
since we only need the %.

Now notice the 4.5 highlighted in Green on first screenshot, that means
we want the 4.5 Away Data for Class A. In this case its 0.

The following Algorythm should be applied to the formula somehow to
make it move to the proper Column.either q or w
(B2-3.5)*6-3

Thats Because sometimes "B2" On gameday sheet might be 5.5


Kinda hard to explain what i need in text but lemme know if you need
anymore info.

Thanks fellas.
 
Would you consider posting your workbook to your tripod site? (Might be
easier with the actual data as opposed to a picture...)

/i.
 
Try this array formula in E2:

=OFFSET(INDIRECT(A2&"!N3"),MIN(IF(NHL!A1:L6=A3,COLUMN(NHL!A1:L6),256))/2,(B2
-3.5)*6-3)

** Remember to Ctrl-Shift-Enter the formula instead of just hitting Enter.

/i.
 
Getting a #Ref error.
Id consider sending you the workbook if it were easier for you.

Thanks
 
It would be a great deal easier... I tried recreating the workbook based on
the info available and the formula worked.

Send it to me at my return address, removing "no" and "spam".

/i.
 
Says your not registered so i couldn't see your profile.
Email me at (e-mail address removed) and ill send it to you.

thanks again
 
Didn't realize that your were using data from another workbook. The
following array formula will work for you if your NHL.xls workbook is open
while working in Gameday.xls:

=OFFSET(INDIRECT("[NHL.xls]"&A2&"!N3"),MIN(IF([NHL.xls]NHL!A2:L7=A3,COLUMN([
NHL.xls]NHL!A2:L7),256))/2,(B2-3.5)*6-3)

** Remember to Ctrl-Shift-Enter this formula.

If NHL.xls will not be open when using this formula, INDIRECT() will break.
You can define some names in your Gameday workbook to get around this...

Post if you need help with this.

/i.
 
Back
Top