Conditional format - blank if zero

[Mel Smith]

Hello folks,

I'm trying to put a conditional format into a linked spreadsheet so that if the source cell is blank, the linked cell is also blank rather than displaying a '0'. The cell formular is "=Sheet1!H5" etc.

Your help will be appreciated.

Mel

 

[taurean]

Try:

[COLOR=blue]=IF(Sheet1!H5="","",Sheet1!H5)[/COLOR]

 

[Mel Smith]

Unfortunately, I get an error messgae stating that I cannot use a reference to another sheet for conditional formatting

Mel

 

[taurean]

Oops
No this is cell formula and NOT conditional formatting!
Sorry for the confusion

 

[Mel Smith]

Can anyone else help?

Mel

 

[taurean]

1. The formula above works if you place it in the cell where you wanted to apply conditional formatting. Did you try that?

2. I do not think that conditional formatting can change content of the cell. It will only change formatting to highlight if the condition is met!

 

[Marcelo Branco]

Hi,

You cannot achieve this with CF. You have to change your original formula (as Taurean posted originaly) to something like:
=IF(Sheet2!A1="";""; < your original formula here ><YOUR here formula original>)

By the way, i think you can use a reference to another sheet for conditional formatting, naming the range of the other sheet (Scope = workbook)

HTH

M.

 

[Mel Smith]

Hi Taurean, Marcelo,

All sorted now. Thanks for your help

Mel

 

[Marcelo Branco]

Hi Taurean, Marcelo,

All sorted now. Thanks for your help

Mel

You are welcome and tks for the feedback

M.

 

[CWatts]

Just to clarify a few alternatives as this can be accomplished with conditional formatting two ways:

First way is when the cell value is equal to zero and just set it as white text on white background.

You can also use a custom number format of "" if the cell is equal to 0 so it wont display anything.