Fill in empty cell if no value...

[albertc30]

Hi all.

I am having some trouble doing something, in my honest opinion, very simple.

Example;
Say B13 has a value, then if B14 is empty I want to fill this in red and if B13 is empty but B14 has a value than fill in red B13.

I am using the following for B13
=AND($B$14=0) "Red"

and for B14
=AND($B$13=0)

My problem now is that if both B13 and B14 are empty I get them filled in red. I do not want this.

How do I go about doing this pleasae?

I think I need to go back to basics.

Regards,
Albert

 

[Snakehips]

Albert,

Change the applies to to $B$13:$H$14, which you have possibly done already.

Then change the formula to =AND(B13="",COUNTA(B$13:B$14)=1)

 

[albertc30]

Albert,

Change the applies to to $B$13:$H$14, which you have possibly done already.

Then change the formula to =AND(B13="",COUNTA(B$13:B$14)=1)

It works mate.

It puzzles me how this works as it only refers to B13 and B14 but the result is also reflected on all other columns till H.

Many thnaks mate.

I shall have a google at COUNTA as I don't have a clue what it is.

Regards,

Albert

 

[Snakehips]

It works mate.

It puzzles me how this works as it only refers to B13 and B14 but the result is also reflected on all other columns till H.

Many thnaks mate.

I shall have a google at COUNTA as I don't have a clue what it is.

Regards,

Albert

Albert,
When you select a range and apply a conditional format formula you structure the formula to suit the first cell of that range.
So with your range of B13:H14 B13 is the first cell and the formula =AND(B13="",COUNTA(B$13:B$14)=1) relates directly to that cell.
You use the $ where appropriate to make any columns or rows Absolute.
Excel then recognises any other references in the formula to be relative and automatically adjusts the formula to each cell in the range in much the same way as it creates relative formulas in cells when you drag a formula over a range.

As for COUNTA(range) it returns a count of how many cells in range are not empty.
Hence part of my above formula checks if B13:B14 etc has only a value of 1, which indicates that the other cell is empty.

I hope that makes sense?

 

[albertc30]

I am almost there, I think.

In this case why not make B13 an absolute reference "$B$13?

Or is it the case as B13 is not absolute in the first part of the formula and as there is a wider range, B13 to H14, excel will change the B13 to B14 and so on and will also change the absolute references int he formula to reflect the initial part of the formula?

Many thanks for taking the time to help and explain Tony.

Regards,
Albert