1. Registered User
Join Date
Aug 2006
Posts
132

I have a monthly calendar worksheet. I also have a "special dates" worksheet in the same workbook. The "special dates" worksheet has columns
A= MonthNr, B = DayNr, [several misc], and E = ShortDesc The data starts in Row 7 and is expandable downward. The data is in order by MonthNr, and DayNr. There are only a few entries for each month and max of one per day.

The Calendar worksheet is laid out like a standard calendar with Columns A-G being Mon-Fri. And with many rows for each week. For each day of the month I want, in one cell for that day, to look into the "special dates" worksheet and display the entry in column E, if one exists.

I put the following formula in for a test (using Ctrl-Shft-Enter to create an array formula). The calendar is Jan (MonNr = 1), F3 is contains the DayNr in the first week's rows.
{=INDEX('Special Dates'!\$E7:\$E100,MATCH(1,('Special Dates'!\$A7:\$A100=1)*('Special Dates'!\$B7:\$B100=F3),0))}

The formula seemed to work fine, however when I copied the formula to a day in the next week's row all the supposedly Absolute Cell References to 'Special Dates' jumped by the number of rows and columns I was from the first test. What am I doing wrong. The formula also needs to be modified to handle when there is no matching row in the 'Special Dates' worksheet.

Second question, is there a simpler (or better) way to accomplish this. I have to copy whatever formula is used to each day in the month.

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi Pete
Originally Posted by Pete Townsend
when I copied the formula to a day in the next week's row all the supposedly Absolute Cell References to 'Special Dates' jumped by the number of rows and columns I was from the first test. What am I doing wrong.
You've made the column references absolute, but not the row references.
For example, here:
Code:
'Special Dates'!\$E7:\$E100
to make the row references absolute you need \$ signs in front of the row numbers like this:
Code:
'Special Dates'!\$E\$7:\$E\$100

Originally Posted by Pete Townsend
The formula also needs to be modified to handle when there is no matching row in the 'Special Dates' worksheet.
If you are using XL 2007 or later, then you can use the IFERROR() function. I think your formula will be along the lines of:
Code:
{=IFERROR(INDEX('Special Dates'!\$E\$7:\$E\$100,
MATCH(1,('Special Dates'!\$A\$7:\$A\$100=C\$2)*('Special Dates'!\$B\$7:\$B\$100=\$F3),0)),"")}
C\$2 and \$F3 I've guessed, because I'm not 100% sure where your days and years are lined up.

If you are using an earlier version than that, then you'll have to use IF() which will make your formula considerably longer.
Code:
{=IF(ISNUMBER(MATCH(1, ('Special Dates'!\$A\$7:\$A\$100=C\$2)*('Special Dates'!\$B\$7:\$B\$100=\$B3),0)),
INDEX('Special Dates'!\$E\$7:\$E\$100,MATCH(1,('Special Dates'!\$A\$7:\$A\$100=C\$2)*('Special Dates'!\$B\$7:\$B\$100=\$F3),0)),"")}
Hope that helps...
Last edited by Colin Legg; 07-24-10 at 19:57.

3. Registered User
Join Date
Aug 2006
Posts
132
Colin,

Col & ROW Absolute. I KNOW THAT. Why didn't I see it. Yuch! My face is red with embarassment.

The calendar is a bit of personal stubbornness. I know there are several free calendar programs I could use, but, as much for learning as for anything else, I did this one myself to fit my personal preferences and to fit my DayPlanner.

Each day is basically several rows. For example week one is rows 3-11, week 2 is rows 12-20, etc. The first row (3) has the date number, the second row (4) has Major holiday names, rows 5-9 are blank, 2nd last row (10) will contain the results of this lookup if any exists, and the last row (11) has the day-of-the-year number. So January 1 of 2010 is a Friday (Col F); F3 shows a "1" F4 shows "New Years Day", F10 is where this formula will go, and F11 shows "1" the first day of the year.

I'm using Excel 2003 so I'll need to encapsulate the formula with
IF(ISERROR,..,..) to account for nothing found, and then encapsulate that with IF(F3>0,..,..) cover blank boxes at beginning & end of Month. I hope that doesn't exceed the limit on variables in a formula. (I think it might)

I'll upload the file for hysterical reasons and to permit plagiarism by anyone that's wierd enough to be interested.

4. Registered User
Join Date
Aug 2006
Posts
132
It turned out that only the IF(ISERROR(Match...,"",INDEX...) was necessary as it would also catch the blank boxes. The final formula was:

=IF(ISERROR(MATCH(1,('Special Dates'!\$A\$7:\$A\$100=3)*('Special Dates'!\$B\$7:\$B\$100=A147),0)),"",INDEX('Special Dates'!\$E\$7:\$E\$100,MATCH(1,('Special Dates'!\$A\$7:\$A\$100=3)*('Special Dates'!\$B\$7:\$B\$100=A135),0))) [note this is for month 3 and the A135 is a particular day number.

Thanks to everyone.
Pete