Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting the ROW() command into a Conditional Format Formula
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)<71 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 -- Regards, Andy Halford |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting the ROW() command into a Conditional Format Formula
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... "ahalford" wrote: 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)<71 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 -- Regards, Andy Halford |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting the ROW() command into a Conditional Format Formula
This works for me with the regional date setting as U.S. English.
=WEEKDAY(--($A$9&"/"&MONTH($A10&1)&"/"&B$9),2)5 -- Biff Microsoft Excel MVP "ahalford" wrote in message ... 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)<71 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 -- Regards, Andy Halford |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting the ROW() command into a Conditional Format Formula
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. -- Regards, Andy Halford "ahalford" wrote: 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)<71 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 -- Regards, Andy Halford |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting the ROW() command into a Conditional Format Formula
Excellent thanks!
-- Regards, Andy Halford "Sheeloo" wrote: 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... "ahalford" wrote: 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)<71 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 -- Regards, Andy Halford |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional format formula | Excel Discussion (Misc queries) | |||
Conditional Format & Formula | Excel Worksheet Functions | |||
nesting conditional formulas | Excel Worksheet Functions | |||
Nesting IF statements, Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional format with a formula | Excel Worksheet Functions |