Excel - get reference to the merged cell above

Question

I have a calendar like rows - fully static - and the days are simple numbers (not dates). [

A also have a list of holidays (Name | Date).

I want to conditionally formmating the days based on the match from the holiday list with the following formula (I10 being 1 - from Jan 1)

=ISNUMBER(MATCH(DATE(_year,month,I$10),HolidayListNamedRange,0)) 

but the issue is that I depend on the month.

Is there a way to determine the month from the day itself (which is a number) and somehow use the row above which is a merged, containing month name? e.g: get cell address for 6, minus -1 to get to the row above and somehow identify the merged cell and get the value.

Hope makes sense! Thanks!

Answer 1

In merged areas, the value is stored in the first cell, which means in this case that the value must be found in the cell above the first date of the month. To find the first date, subtract the number stored in the column from the column number.

=COLUMN(L2)-L2+1

This find first day from 09.01 in my sample.

And with the INDEX function we find the month name.

=INDEX(1:1,0,COLUMN(L2)-L2+1)

This find 'January' from 09.01 in my sample

Next, to convert the month name into a number, use the following formula:

=MONTH(DATEVALUE("1"&LEFT(INDEX(1:1,0,COLUMN(L2)-L2+1),3)))

This returns 1 from 09.01 in my sample.

Sample:

Answer 2

Well the merged heading occupies the left-most position of the merged cells so you need to find the last string which is on or before the current date, calculate the date using that information, and lookup the result in your Holidays range:

=COUNTIF(Holidays,DATEVALUE(D10&INDEX($D9:D9,MATCH("zzz",$D9:D9))))

or

=COUNTIF(Holidays,DATEVALUE(D10&HLOOKUP("zzz",$D9:D9,1,TRUE)))

assuming that the merged cell starts in the same column as the first day of the month (can't quite see it on your screenshot).