Format to return a blank cell

[garry]

Hi,

Thanks for reading this post.

I have an Excel spreadsheet which contains a formula which draws its data from another spreadsheet.

=Income!O7

I'd like to format that cell so that it returns a number with two decimal points (red if a minus figure) but blank if there is no number in the source spreadsheet.

Any help would be greatly appreciated.

Thanks,

Garry

[#2]

@garry You mention "but blank if there is no number in the source spreadsheet". Would it be that there is a number or a an empty cell? Or could there be a text in the source cell?

In case of the first option, you can achieve all in one go by custom formatting like: 0.00;[Red]0.00;

The above picture is taken on a Mac, but it's similar for PC.

If, on the other hand, the second option may occur, you would need a formula like this:

=IF(ISNUMBER(Income!O7),Income!O7,"")

and custom format the same as above.

[#3]

Hi @Riny_van_Eekelen 

Thank you for your response, unfortunately neither option provided me with exactly the right result however, it did encourage me to try lots more formatting options and I eventually came up with a custom format which seemed to produce the required format (although I'm not sure how I got to it).

_-* #,##0.00_-;[Red]-* #,##0.00_-;"";_-@_-

Regards,

Garry

[#4]

@garry 

In general you may simplify format to

#,##0.00;[Red]-#,##0.00;;