Hello All, Happy New Year... Need help for Conditional formula.

[bhasoriya]

Hello Friends, once again Happy New Year to all.

I need your help ..


I have just prepared one calender. I am making school attendance register.

explaining here details.

A column = Roll No,

B Column = Student Names,

Month-January in C2,

C3 to AR3 weekdays. Su-Mo-Tu-We to Sa,

C4 to AR4 Dates,

Now what i want, If date has sunday then that entire column should be Gray colour.

Januray 1st,8th, 15th, 22nd & 29th are sunday then Column C,J,Q,X,AE should be gray colour.


I had try with =WEEKDAY(C1)=1, but it works in only one cell.

 

[shrivallabha]

Hi Bhasoriya,


You can copy down the conditional formatting applied in one cell to all cells. It should work. Only thing would be row number shall be made absolute e.g. =WEEKDAY(C$3)=1 and then applied down and across!


But keep in mind conditional formatting is volatile by nature which makes sheet calculate more times so it would be good idea to apply it to "height" of the column.

 

[sgmpatnaik]

@bhasoriya


Hi


you could try on your formula as like


=WEEKDAY(C$3)=7


AND COPY OR DRAG FROM C3:AR3


THANKS


sp

 

[bhasoriya]

Hello Shrivallabha,


Yes i got an idea from this fomula, but facing another problem. When i used this formula in next sheet it doesn't support. I mean.. when i apply this formula from reference sheet 1, it is showing an error.. " you cannot use reference to other worksheet or workbook for conditional formatting criteria"


it doesn't support Any idea..?


you can download and see the file how i create, and get an idea..

https://rapidshare.com/files/3887003410/School%20attendance.xlsx

 

[shrivallabha]

Bhasoriya Hi,


It means: you have to write conditional formatting for individual sheet. You can use named ranges for cross-sheet referencing but I really don't think its necessary here.