Nesting the ROW() command into a Conditional Format Formula

[ahalford]

Hi,
I have a table with the months (January - December) on the right (Column A)
and the numerical days of the month (1-31) across the top (Row 9). Cell A9
contains the year (2009)

I want to condition the format of each cell that represents a weekend day to
be yellow fill.

I have the conditional formula(s) that performs this on cell B10 as:

=WEEKDAY((DATEVALUE(CONCATENATE(A10,"/",B9,"/",A9))),1)=1
=WEEKDAY((DATEVALUE(CONCATENATE(A10,"/",B9,"/",A9))),1)=7
=WEEKDAY((DATEVALUE(CONCATENATE(A10,"/",B9,"/",A9))),1)<7>1

I am trying to replace the A in cell reference A10 with a reference of
"ROW()" to give me the month of same row as the reference cell, and then also
replace the B in cell reference B9 with a reference of "COLUMN()" to bring in
the correct data.

can anybody help, or recommend another way to arrive at the solution?

Thanks in advance

 

[Sheeloo]

Replace A10 with
INDIRECT("A"&Row())
thiis will refer to A9 if row() is 9, and so on
Similarly for Column reference you will have to use INDIRECT with LOOKUP to
translate the Column() to a letter
INDIRECT(LOOKUP(COLUMN(),{1,2,3},{"A","B","C"})&"1")
to get A1 for Col A, B1 for Col B and so on... add more to the array if you
want to go beyond Col C
you can also use VLOOKUP instead of LOOKUP if columns to lookup are too many
to list...

 

[T. Valko]

This works for me with the regional date setting as U.S. English.

=WEEKDAY(--($A$9&"/"&MONTH($A10&1)&"/"&B$9),2)>5

 

[ahalford]

OK, here is an update: it appears that the formula works as it is when
applied to the entire range, however: the month column must be formated as a
number from 1 thru 12 and not the months themselves. January worked on my
testing but the others did not hence my confusion.

My issue now is how to have the value of 1 thru 12 assigned to teh cell but
to display the month name January thru December.

 

[ahalford]

Excellent thanks!
--
Regards,
Andy Halford

Replace A10 with
INDIRECT("A"&Row())
thiis will refer to A9 if row() is 9, and so on
Similarly for Column reference you will have to use INDIRECT with LOOKUP to
translate the Column() to a letter
INDIRECT(LOOKUP(COLUMN(),{1,2,3},{"A","B","C"})&"1")
to get A1 for Col A, B1 for Col B and so on... add more to the array if you
want to go beyond Col C
you can also use VLOOKUP instead of LOOKUP if columns to lookup are too many
to list...