Is there a automated way of highlighting cells whose adjacent cell is blank?

[JONPM]

Is there a automated way of highlighting cells whose adjacent cell is blank?

AND THEN input data (in this case "b.") into the highlighted cells?

I would have though that perhaps by utilising a combination on CONDITIONAL sorting/formatting and the =& function might do the trick?? ?


before:
A B C D

	1930	2006
	1956	2004
	1920
	1918	1987
	1904	1990
	1970
	1968
	1942	1999
	1870	1934

<tbody>
</tbody>

after formula/procedure:

	1930	2006
	1956	2004
	b.1920
	1918	1987
	1904	1990
	b.1970
	b.1968
	1942	1999
	1870	1934

<tbody>
</tbody>

Many thanks in advance

 

[Special-K99]

Conditional formulas highlight cells dependent on conditions.
What you're doing is changing the VALUE of a cell

Use a Helper column
in E1
=IF(C1="","b."&B1,B1)
and copy down column E

 

[JONPM]

That is fantastic — thank you!

Is there anyway of extending this formula so that in the instances where BOTH cells in column B and C are empty NO DATA is entered. As in:


before:
A B C D

	1930	2006
	1956	2004
	1920
	1918	1987
	1970
	1968
	1942	1999
	1870	1934

<tbody>
</tbody>

after formula/procedure:

	1930	2006
	1956	2004
	b.1920
	1918	1987
	b.1970
	b.1968
	1942	1999
	1870	1934

<tbody>
</tbody>

It is not possible to delete the row as in the real worksheet there would be data (irrelevant to this sort) in Column A

Many thanks !

 

[Mowgy]

=IF(AND(B1="",C1=""),"",IF(C1="","b."&B1,B1))

Does that work ?

 

[Peter_SSs]

=IF(AND(B1="",C1=""),"",IF(C1="","b."&B1,B1))

Does that work ?

Should work, but note that you are testing C1="" twice.
Should be able to simplify to

=IF(C1="",IF(B1="","","b."&B1),B1)

 

[Mowgy]

Thanks Peter, I am good at doing things twice over... :P