Double LOOKUP formula....??

[HuskerBronco]

I have a named range (BYE TEAMS) that looks similar to this:

...........A....................B....................C....................D....................E
1 Oct 2, 2007......Jacksonville.....New Orleans.....Tennessee......Washington
2 Oct 9, 2007........Cincinnati .......Minnesota.........Oakland.......Philadelphia
3 Oct 16, 2007.......Buffalo............Denver............Detroit.........Indianapolis
4 Oct 23, 2007......Carolina.........Cleveland........Green Bay........San Diego

Sheet1 A1:AA1 is a list of dates.

I need a formula, in SHEET1-B2 that will do the following:

1. Get a value from a specific cell...(say SHEET3-A1)
2. See if that value appears in the BYE TEAMS range in the row matching the date in cell SHEET1-A1.
3. If it does...print "B-" into cell SHEET1-B2.
4. If the value does not appear in the row...then return SHEET3-A1.

What series of LOOKUP, MATCH, VLOOKUP...etc. do I use to achieve this?

[rylo]

Hi

Here's one way

Sheet1!B2: =IF(AND(ISNUMBER(MATCH(A1,OFFSET(bye_teams,0,0,100,1),0)),ISNUMBER(MATCH(Sheet3!A1,OFFSET(bye_teams,0,0,1,100),0))),"B-",Sheet3!A1)

1) The range name is bye_teams. Defined names can't have spaces.
2) I've made a guess at the size of the defined name to be 100 rows / 100 columns. Adjust as required.

HTH

rylo

[HuskerBronco]

Excellent, rylo...!!!

I entered your formula into the top-left cell of my grid...made the necessary changes to conform the formula to my particular worksheet...copy/pasted it down and across the rest of the grid...and......TA!!! DA!!!......it worked!!!

But, only partially.........

It seems that regardless of the date that I point to in each column, the formula only uses the FIRST ROW from BYE_WEEK_TEAMS. I have zipped a copy of my spreadsheet so you can see this in action. You will notice that the B- appears in place of the SAME FOUR teams throughout the entire grid...(the four teams listed in the FIRST row of the named range). What do I need to change to prevent this behavior?

Also,

Something else I would like to do, is reformat the cell alignment to GENERAL for all of the cells that return a "B-". Apparently, I cannot use conditional formatting to accomplish this...(using Excel 2000). I am going to have to write a Macro to perform this task...(something that I am completely new to). Additionally, I need the cells that DON'T return a "B-" to revert to the CENTER alignment used for the rest of the grid.

I suppose I could manually select each of the affected cells and format them accordingly...but I would have to do this EACH TIME THE SHEET IS RECALCULATED......(which, obviously, would be a pain in the butt).

Are you experienced enough with VBA to point me in the right direction to create this Macro? I surely hope so...and I can't tell you how much I appreciate your help thus far. It looks like I might be able to get this contest up and running, after all. My goal, was to create a spreadsheet that could be used year after year, with minimal changes to the content of the spreadsheet. Thank you, again, rylo....for helping me achieve that goal.


Note:

1) The range name is bye_teams. Defined names can't have spaces.

The range names in my spreadsheet are not without the necessary underscores.
I neglected to include them in my initial post. Sorry about that.... .

[rylo]

HI

OK, try this.
1) L5: =IF(AND(ISNUMBER(MATCH(M$4,OFFSET(BYE_WEEK_TEAMS,0,0,10,1),0)),ISNUMBER(MATCH(Sheet2!N2,OFFSET(BYE_WEEK_TEAMS, MATCH(M$4,OFFSET(BYE_WEEK_TEAMS,0,0,10,1),0)-1,0,1,10),0))),"B-",Sheet2!N2)

I copied it down to L36 and I think it was bringing back the correct results.

2) Right click on the sheet tab, select view code and put in the following event code

Please Login or Register  to view this content.

It uses the calculate event to fire.

HTH

rylo

[HuskerBronco]

Fantastic, rylo....!!!!

I'm on my way to work, just now...so will try your formulas in the morning.

Don't see any reason they wouldn't work.

Thank you, so much.

Regarding the VBA...It never dawned on me that I needed to use an event handler to trigger the desired action. But, that makes perfect sense. Even still, I never would have figured out which event handler to trigger it with. Thanks, so much, for the time taken to help me with this.

[HuskerBronco]

Stayed too long at the lounge, yesterday.
Was too liqoured up to work on my spreadsheet.... .

Just finished making the changes you suggested, rylo.

Your VBA worked perfectly. Exactly what I wanted.

I pasted the new formula into the appropriate cells, only to discover that the formula didn't return "B-" in the spots that it should have. In fact, there wasn't a "B-" to be found, anywhere on the grid. The grid was populated with team names throughout. Even the teams that had byes on that week showed the team name, rather than "B-".

I opened the formula palette and tabbed through the formula to see if I could locate the section of the formula that was incorrect. Everything looked good until I got to the 2nd OFFSET function, where I encountered a #NAME error for the first argument of the function. I looked at the formula in the REFERENCE box, and discovered that there was an erroneous SPACE in the range name. I removed that space, pressed enter...and a "B-" appeared in the cell. PERFECT.....!!!!

I pasted the amended formula into the rest of the grid...and everything appeared to work perfectly. The grid was populated with team names, with the exception of the bye teams for each week. In place of the bye team names...my spreadsheet was showing a "B-". Furthermore, the bye teams were properly located in the BYE_WEEK_TEAMS range...so that each week, the "B-" appeared in the proper cells.

Then I stumbled onto a problem....

In week 6 (October 15), there are SIX teams with a bye, rather than four. The formula is only capturing the FIRST FOUR teams from the BYE_WEEK_TEAMS range. Two teams...(in this case, Pittsburgh and San Fran), are not captured by the formula...and return the team name rather than a "B-". This occurs again, in week 8 (October 29).

What change do I need to make to your formula to correct this behavior?

After correcting the formula...my spreadsheet will be completed, and ready to be distributed. Thanks to you, rylo....

But...I would like to add one more feature to the spreadsheet. On sheet two, under my table listing the BYE TEAMS, I have tables listing teams that play NIGHT GAMES. I would like to format each cell in the grid to check the date at the top of the column (just like your BYE WEEK formula) and see if that particular team appears in each of the NIGHT GAME tables.

For example:

If the team name in cell SHEET1!D5 appears in the MONDAY NIGHT GAMES table for the date shown in SHEET1!E4...I want that cell specially formatted. What formula would I enter into the conditional formatting dialog box to achieve this?

I am in total awe of how easy you guys (and gals) make all this stuff look. I've been working with Excel for a couple of years, now...and I still can't put together formulas and VBA the way you guys do. My Excel reference books have taken a beating...while you guys just spit this stuff out like it's nothing. I can only dream of becoming as proficient with Excel as you, rylo.

Thanks, again....for taking time to share your knowledge with us incompetents.

[rylo]

Hi

1) Change the 10 in the formula to 20. This should cover the number of columns you have in the table.

2) Interesting idea. As you only have 3 night blocks, you could use the same formula approach I've used in the bye formula to determine if the date is in a block, then the team is in a block. You would have to repeat this 3 times for each cell (one for each of the night blocks) and if there was a match, return a number (either 1,2 or 3 say). The result of this could be used in the conditional format.

How to make that happen???
a) Build a mirror sheet to sheet1 and have the formula calculate a result. Cumbersome, but better than trying to do it in the cell.
b) Build your own function to do the same sort of thing and this could be used in the Conditional Formatting (I think).

Haven't got time to fiddle at the moment. Do you have a preference for the approach?


rylo

[HuskerBronco]

When I first encountered the problem of the last two teams of a SIX team bye week being ignored...

The first thing I tried...was to eliminate the "10" argument altogether....leaving the formula...(so I thought)...to default to the column parameters of the NAMED RANGE.

Didn't work....

Then I tried "20"....so as to encompass the entire NAMED RANGE.

Still........didn't work.

Don't know where to go from there.


Maybe I'm doing something wrong.

[rylo]

Hi

Have a look at the attached file. Seems to be working. There are 6 B- for 15 Oct and 29 Oct.

1) Changed the 10 to 20 as mentioned before.
2) Had to change "San Fran" to "San Francisco " in the Sheet2!D5:AL33 to match the entry in the drop down. Note that there is a trailing space.

Didn't check the other names, but it may pay to make sure they are exactly matching - including the trailing spaces....


Have you given any thought on how you want to approach the conditional formattin???

HTH

rylo

[HuskerBronco]

2) Had to change "San Fran" to "San Francisco " in the Sheet2!D5:AL33 to match the entry in the drop down. Note that there is a trailing space.

I suspected something like this might be the problem. I had shortened San Francisco to San Fran to fit the team names into smaller cells. I thought that I had made all of the necessary replacements...but, apparently I missed a few. Since it was "San Fran" that was causing the problem, I MAY have had a working formula, all along. I was simply checking WEEK 6 for San Fran...and if I saw it, instead of a "B-", I assumed that the formula wasn't working. I never checked further for Pittsburgh.

I used AbleBits "Trim Spaces" Add-In to clean up the sheet...then made certain that Validation was properly applied to all affected cells. I recalculated the workbook...and everything worked perfectly.

This experience served as a reminder to myself, and to any others viewing this thread, that it's very important during construction of formulas, VBA code, HTML code, PHP code, etc.....to not have erroneous spaces in the code OR in your referenced data.

As mentioned above, I used an nice little Excel Add-In from AbleBits.com to clean up the work sheets. This Add-In will "Trim Spaces" from all selected cells at the click of a button. "Trim Spaces" is free...and you can download it by clicking the links in this post.


As for the formatting of specific cells to reflect NIGHT games...it doesn't matter how I achieve this. I thought that I would insert a Macro that would format the cells each time the worksheet is recalculated. I only need THREE different formats...one for Monday Night games, one for Sunday Night games, and a third for Thursday, Friday and Saturday Night games.

Should I go the Macro route, use Conditional Formatting, or use some other procedure? I eagerly await your advice.

Thanks, once again, rylo...for your time and expertise.

[rylo]

Hi

Have a look at the attached and see if it will do.

I've put in a worksheet calculate event that calls a macro called SetNiteColor. This basically will set the cell font for the team that have a night game.

I've had to convert the formulas you have in sheet2 for the dates to values. So E77:E92 are now values, not formulas. Had trouble with the find statements when they were formulas, and this was the easy way to resolve.

Have fun.

rylo

[HuskerBronco]

Hello rylo....

Sorry it took so long for me to get back to you.

I saved your file to my desktop...opened it...and clicked ENABLE MACROS.
The workbook opened to SHEET 1...and the Night Game teams were a different font color across the grid.....but only for a second or two. Then they reverted back to xlAutomatic.

I pressed F9 to recalculate the worksheet...no change...still xlAutomatic font color. So I manually ran the Macro "SetNiteColor"....and still no change.

It looks like this code will suit my needs perfectly once I get it to run the way it is intended. I stepped into the Macro to see if I could figure out how the Macro was constructed, and maybe locate the problem. Your notes were greatly appreciated, as they helped me to understand what the Macro was attempting to do each step of the way. While most of the code was simple enough to figure out...there were parts of the code that went right over my head. Therefore, I couldn't edit the code to correct the problem described above.

Could you take a look at the file, once more, and see if you can find the source of my troubles. Thank you, so much rylo.

[rylo]

Hi

I've rerun it a couple of times and can't see anything odd. It is coloring the teams based on the data on sheet2. I've run the "Press this button to randomize teams", then gone back to sheet 1, manually recalculated, and the teams colored according to the dates.

Can you step through the code. I'm guessing that it is setting all the fonts to the default, and then not finding any of the dates. Can you check that it is actually finding the dates and moving into that part of the code to action the font change? If it is not, then I'd guess it has something to do with the date formats between US and AUS.

Let me know if this is where it seems to be hanging up...


rylo