Conditional Formating, formating cell depending on data frm other cell 2

[Triacona]

Dear all,

I have a spreadsheet that has a lot of colums and rows.

For ease of use I want when the user has entered Y in F2 then I want C3 to be highlighted (yellow fill pattern) for example.

How would I go about this?

I have at current conditional formating with the following parameters formula is =$F$2:$F$87=Y.
This conditional formating is on C2:C87
This is not working.
I have also tried =$F$2:$F$87="Y"

Please help...

Thank you!

Kind regards

Triacona

 

[SkipVought]

Hi,
Select C2:C87.
[tt]
=UPPER(F2)="Y"
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Triacona]

Hi Skip,

Thank you very much for your reply.

I have used

=UPPER(F2)="Y"

after selecting C2:C87 and clicking Format -> Conditional Formatting

Then I have used =UPPER(F2)="Y" by choosing Formula from the drop down and inputting =UPPER(F2)="Y" in the Formula bar

Is this the way I should do it?

What does UPPER mean?

I am trying to shade the cells (C2:C87) one by one yellow if any of F2:F87 equals "Y".

I.e. if F2 is Y at that point I want C2 to shade yellow.

Thanks for your help

Kind regards

Triacona

 

[SkipVought]

What does UPPER mean?

Return the UPPER CASE value of what is in the referenced cell, just in case someone enters a LOWER CASE y.

Is it working for you?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Triacona]

Dear Skip,

Thank you

But the problem is only partially resolved...

The formula you provided only works by cell shading when each individual cell has specific conditional formating done to it.

I can only do this for 1 cell to the another cell, not multiple cells to multiple cells.

i.e. if F2 = "Y" then C2 = colour yellow...
if F3 = "Y" then C3 = colour yellow...
if F4 = "Y" then C4 = colour yellow...
etc.(single cell conditional formating)
This is what I want to happen (above). BUT in MULTIPLE FORMAT.

So I want to choose C2:C87 then click Conditional Formating, then enter the FORMULA =UPPER(F2:F87)="Y" apply formating...
BUT this does not work.
So what I want is EACH individual cell to check the adjacent cell to see if it equals Y and then shade accordingly, but I don't want to do this for each cell...as there are many


Thanks for your help, any more help would be greatly appreciated.

Kind regards

Triacona.

 

[SkipVought]

THIS WORKS AS INSTRUCTED.

Select the range you want CFed

In the FORMULA cell in the CF, assuming that the selected range starts in row 2
[tt]
=UPPER(F2)="Y"
[/tt]
Set the FORMAT

FINISH

It WORKS for ANY row in the selected range that has a y in column F!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Your problem may be that you have CALCULATION set to MANUAL, rather than AUTOMATIC.

OR...

you could hit F9 (CALCULATE) each time you make a change.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Triacona]

Dear Skip,

Thanks so much for your help

My calculation is set to Automatic.

I think you misuderstand what I want.

To achieve what I want I at current have to,

1. select a single cell, then

2. select Conditional Formating, then

3. put in the formula =UPPER(ADJACENT CELL)="Y"

This works and then highlights one cell.

I have done the following chose C2:C87 then,

1. clicked Conditional Formating

2. in the formula input =UPPER(F2:F87)="Y"

3. my hope is tha that this will then calculate as such...
if F2 ="Y" then shade C2
if F3 ="Y" then shade C3
if F4 ="Y" then shade C4
if F5 ="Y" then shade C5
if F6 ="Y" then shade C6
ECT.

BUT THIS IS NOT WORKING even with Auto-Calculate on.

Is this the way to do it, or must I try something diferent?

Thanks again for your help

Kind regards

Triacona

 

[Gruuuu]

Triacona. Your problem comes from the difference between absolute and relative references.

Note that Skip's instructions has a reference of "F2" instead of "$F$2" The difference is, when you copy that formatting to a new cell, the formatting with "F2" automatically becomes "F3". However the cell with a formatting of "$F$2", it stays the same. those "$" signs keep the references from changing. Note that each "$" sign affects a different part of the reference. the "$" before the column locks the column, and likewise for the row.

Does this help?

Follow Skip's instructions to the letter. Your very first conditional format should look like

=UPPER(F2)="Y"

Then, your next one should look like

=UPPER(F3)="Y"

That should happen automatically.

 

[Gruuuu]

...let me clarify further.
If you have several columns selected, one of your cells will be the active cell. It will not be light blue like the other selected cells.

MAKE SURE that your conditional formatting is set up correctly FOR THAT CELL without absolute references, and your formats will automatically fill in correctly.

 

[Triacona]

Dear Skip and Gruuuu,

Thank you ever so much for your help.

It was the formating absolute and relative.

So my Conditional formating formula on C2:C87 is:

=UPPER(F2:F87)="Y"

This then shades C2:C87 if the corresponding F cell is Y
I.E. if F2="Y" then C2 is shaded
ETC.

Thanks again.
Kind regards

Triacona

 

[SkipVought]

The instructions were ABSOLUTELY CLEAR.

F2 is NOT $F$2

=UPPER(F2)="Y" is NOT =UPPER(F2:F87)="Y"



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

So my Conditional formating formula on C2:C87 is:

=UPPER(F2:F87)="Y"

This then shades C2:C87 if the corresponding F cell is Y

This is absolutely incorrect!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Well, it works. Because the reference just reads the value of the first cell, which happens to be the one Triacona is interested in.

It's definitely sloppy, but really not worth getting worked up over.

 

[SkipVought]

Other members browse these forums and make decisions based on the information therein.

Yes, it works for THIS particular instance, but may not yield expected results in another instance.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}