Skip blank columns with LOOKUP formula
 

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

Skip blank columns with LOOKUP formula
 

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.

Skip blank columns with LOOKUP formula
 

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

Skip blank columns with LOOKUP formula
 

1 way to use the function:

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

Skip blank columns with LOOKUP formula
 

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

Skip blank columns with LOOKUP formula
 

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

Skip blank columns with LOOKUP formula
 

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??? :confused:




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.


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

Skip blank columns with LOOKUP formula
 

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)*(Sch edules!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"
wrote in message
...

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??? :confused:




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.


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