Try this entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

=INDEX(Schedules!B$3:IU$149,MATCH(A$1,Schedules!A$ 3:A$149,0),SMALL(IF((Schedules!A$3:A$149=A$1)*(Sch edules!B$3:IU$149<""),COLUMN(Schedules!B$3:IU$149 )-1),ROWS($1:1)))

Copy down until you get #NUM! errors meaning the desired data has been
exhausted. Then you can just delete those excess formulas.

Since each team plays on average 10 - 12 games during the season and you
apparently have 147 teams that means you'll need about 1470 of these
formulas. This will be slow!

Why do you have so many dates? The season isn't that long! It would speed
things up if you could reduce the size of the master schedule.

Biff

"Biff" wrote in message
...
The formula to do this isn't that difficult, HOWEVER, due to the size of
the file it will be slow!

I'll post a formula after golf is over!!!!!

Biff

"HuskerBronco"
wrote in message
news:HuskerBronco.2cun05_1156095008.5833@excelforu m-nospam.com...

Hello, people...

I'm having a heck of a time devising a formula for a seemingly simple
task, which I will outline below:

I've got a page in my workbook named "Schedules". On this page, B1:IU1
is a series of dates...specifically, dates on which a college football
game may be played. A list of college football teams is alphabetically
entered in cells A3:A149. I have entered the OPPONENT for each team in
the column corresponding to the date that the game will be played.

Example:

Cell A3 = "Air Force"
Cell Q1 = "September 9" ---(This is the date of Air Force's first
game)
Cell Q3 = "Tennessee" ---(Air Force's first opponent)

Now, here's what I'm trying to do:

I have a seperate page in my workbook for each team. The page named
"Air Force" has the team name (Air Force) entered into cell A1. On
each team page, there is a table for that team's schedule. What I want
to do, is write a formula that will MATCH the value in cell A1 (Air
Force), with the list of teams in Schedules!A3:A149. Then, look across
that row for the FIRST column with data...which, in the example, above,
would be column Q. The value in the FIRST column with data should then
be returned to the first space in the table for that particular team's
schedule. In the example...the first space on the schedule for Air
Force would show the value "Tennessee".

Then, I want to drop down one cell, and in that cell return the value
for the SECOND cell in a row that contains data. And so on....

This way, I can enter the entire season's schedule on the "Schedules"
page, and the schedules on each individual team page will be entered
automatically.

If this makes any sense at all, give me some idea of a formula that
might accomplish this. I have tried numerous combinations of MATCH,
VLOOKUP, HLOOKUP, INDEX,....and God-knows-what-else. :(

Point me in the right direction, please. :)

Thanks, in advance, for any help you can give me, here.


--
HuskerBronco
------------------------------------------------------------------------
HuskerBronco's Profile:
http://www.excelforum.com/member.php...fo&userid=4803
View this thread:
http://www.excelforum.com/showthread...hreadid=573560