Could you post a copy of the NHL schedule as you have it? (Just zip it, then attach the zip)
For example, if the data is like this in Columns A/B on another sheet, like this for each team:
You'd have to name the range for each team. So if the above was the schedule for Anaheim, you could name the range ANA (same as your abbreviation) and put in K3:
=IF(ISERROR(VLOOKUP(K$2,INDIRECT($F3),2,FALSE)),"-", VLOOKUP(K$2,INDIRECT($F3),2,FALSE))
and copy down and across.
If you had it like this (a complete schedule by date):
Then you'd need to do something more involved. (Which I won't get into right now unless it's the case)
If you just have 140 columns that look exactly like what you already have on your spreadsheet (ie. the teams will be in the same relative order, it just goes on for the full season instead of 28 days), you can name the whole thing NHLSchedule. (Ie. I'm assuming it's on another sheet, with the teams in column A, the dates in row 1. You'd highlight the whole thing and name it. The first column wouldn't need to be included, but it wouldn't hurt anything.)
In K3, you'd put:
=HLOOKUP(K$2,NHLSchedule,ROW($F3)-ROW($F$3)+2,FALSE)
or alternatively so it has error checking so it looks pretty at the end of the season:
=IF(ISERROR(HLOOKUP(K$2,NHLSchedule,ROW($F3)-ROW($F$3)+2,FALSE)),"-",HLOOKUP(K$2,NHLSchedule,ROW($F3)-ROW($F$3)+2,FALSE))
Scott
Bookmarks