Skip blank columns with LOOKUP formula

[HuskerBronco]

Hello, people...

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

I've got a page in my workbook named "Schedules". On this page, B1:IU
is a series of dates...specifically, dates on which a college footbal
game may be played. A list of college football teams is alphabeticall
entered in cells A3:A149. I have entered the OPPONENT for each team i
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 firs
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 name
"Air Force" has the team name (Air Force) entered into cell A1. O
each team page, there is a table for that team's schedule. What I wan
to do, is write a formula that will MATCH the value in cell A1 (Ai
Force), with the list of teams in Schedules!A3:A149. Then, look acros
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 the
be returned to the first space in the table for that particular team'
schedule. In the example...the first space on the schedule for Ai
Force would show the value "Tennessee".

Then, I want to drop down one cell, and in that cell return the valu
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 entere
automatically.

If this makes any sense at all, give me some idea of a formula tha
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

 

[excelent]

Hi HuskerBroco,- yeahh that 1 is hard to fix, I tryed fix a formula without
luck
so u have to use this instead for a while if u like

put code in a ordinary module and find first not emty cell in row 3 after
column-B
Type in a cell : =kol("Schedules!b3")

Function Kol(rk As String)
Application.Volatile
Kol = Range(rk).End(xlToRight).Column
End Function

I think it wil help u som of the way
best regards PM.

 

[Biff]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[excelent]

1 way to use the function:

=INDIRECT("Schedules!"&kol("B"&MATCH(A1,Schedules!A1:A100,0))&MATCH(A1,Schedules!A1:A100,0))

 

[excelent]

UPS Forgot to say i change the Function a bit

Function Kol(rk As String)
'Find first not emty cell in row 3 after column-B,- Type in a cell :
=kol("Schedules!b3")
Application.Volatile
Kol = Chr(64 + Range(rk).End(xlToRight).Column)
End Function

 

[Biff]

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)*(Schedules!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

 

[HuskerBronco]

Thank you, so much, guys...!!!

excelent...look below. I have a separate question for you.

The formula provided by you, Biff, was perfect. But, I need to carry
this formula one step further.

You asked why I had so many dates listed in my worksheet. In reality,
columns in Schedules!B:IU are not all dates. For the months of late
August thru early December, I have eliminated all dates that fall on
Monday or Tuesday night. More than likely, I will also eliminate the
Wednesday dates, as well. After the first week of December, ALL dates
thru the first week of January are included, since BOWL games may fall
on any day of a given week in that time period. Eventually, I will go
thru the BOWL game schedule and eliminate the dates that do not have a
bowl game scheduled.

Also, as I stated earlier, Schedules!B:IU do NOT consist of dates
alone. The column preceding each date on the worksheet is an empty
column for a HOME or AWAY designation. An @ is placed in this column
for AWAY games, and the column is left blank for HOME games.

For example:

B3 = (blank) C3= Tennessee
means a team plays Tennesse AT HOME on that particular date (C1).

B3 = @ C3= Tennessee
means a team plays AT TENNESSEE on that particular date (C1).


This is where I need to carry your formula a bit further.....and, I
hope this doesn't get too complicated. What I need is for the formula
to not only ignore BLANK cells...but, also cells that contain an "@".
This will return TEAM NAMES only to each spot on the individual
schedules. Is this even possible???




excelent....here's my question for you. You suggested the following
formula:


=INDIRECT("Schedules!"&kol("B"&MATCH(A1,Schedules!
A1:A100,0))&MATCH(A1,Schedules!A1:A100,0))

That formula contains my first experience with the use of kol. What is
kol...??? and, where can I learn more about it?? Thanks for your
info.

 

[Biff]

Try this:

Still array entered: CTRL,SHIFT,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)*(Schedules!B$3:IU$149<>"")*(Schedules!B$3:IU$149<>"AT"),COLUMN(Schedules!B$3:IU$149)-1),ROWS($1:1)))

Replace "AT" with the "@" sign. Include the quotes. If I use the real @ sign
in the formula in this post it ends up hyperlinking. Who designs this
software?

Biff

"HuskerBronco" <[email protected]>
wrote in message