conditional format 'IF'

[jammoca]

I'd like a way to do this ....

If Subject1!C5 has the same date as ENTRY!V$2 then Subject1!F5 should be formatted with a black fill and display the text found in ENTRY!AB$2.

then if Subject1!C6 has the same date as ENTRY!V$2 then Subject1!F6 should be formatted with a black fill and display the text found in ENTRY!AB$2.

then if Subject1!C7has the same date as ENTRY!V$2 then Subject1!F7 should be formatted with a black fill and display the text found in ENTRY!AB$2.

ETC ETC ETC .. all the way down the page.

I don't seem to be permitted to do this with Conditional Formatting.

Is there another way ?

Kind regards,

Chris

 

[liveinhope]

I've got this to work (sort of... ) In EXCEL 2007 at least conditional formatting cant refer to another worksheet or workbook . Not sure if any other version allows this

- in cell $M$1 on SUBJECT1 I entered a formula = 'entry'!$v$2

- formula in column F =IF(C2=$M$1,entry!$AB$2,"")

- then conditional format on column F use "formulal" option =$C2=$M$1 . ( then set up the format as fill black)
- format column F cells with font colour other than black

 

[jammoca]

great idea, but I already have text written into cells in column F so the conditional format would have to be an 'over-ride' where it replaces whatever is already written into that cell with what is in ENTRY!AB2

any other ideas ?

 

[liveinhope]

So what you want to do is to conditionally replace the values in column F and then format the cell as black fill, white font
as below ?


If Subject1!C5 has the same date as ENTRY!V$2 then Subject1!F5 should be formatted with a black fill and display the text found in ENTRY!AB$2.
If not then leave value in subject1!f5 and the formatting of subject1!f5 as is

then if Subject1!C6 has the same date as ENTRY!V$2 then Subject1!F6 should be formatted with a black fill and display the text found in ENTRY!AB$2.

If not then leave value in subject1!f6 and the formatting of subject1!f6 as is

<COLGROUP><COL style="WIDTH: 593pt; mso-width-source: userset; mso-width-alt: 28928" width=791><TBODY>
</TBODY>

then if Subject1!C7has the same date as ENTRY!V$2 then Subject1!F7 should be formatted with a black fill and display the text found in ENTRY!AB$2.

If not then leave value in subject1!f7 and the formatting of subject1!f7 as is

<COLGROUP><COL style="WIDTH: 593pt; mso-width-source: userset; mso-width-alt: 28928" width=791><TBODY>
</TBODY>

 

[jammoca]

correct

 

[liveinhope]

can you use/add another column

- leave column F "as is "
- put (amended) formula =IF(C2=$M$1,entry!$AB$2,F2 ) in the "new " column (I used "G")
- then enter the conditional formatting formula column "G" (white font , black fill )
- you will/may also need a 2nd conditional format for "G" to display the unchanged values as black font , white fill