Linked sheets problem

[Excelnoobster]

Hi. I have a sheet linked to another sheet. If a cell from the source sheet is blank the second sheet displays a 0. Is there a way so set up the second sheet to display a blank istead of a 0. Thanks.

 

[Andrew Poulsom]

You can use a formula like:

=IF(Sheet1!A1,Sheet1!A1,"")

or you can suppress zeroes with a number format like 0;0;.

 

[Excelnoobster]

Thanks for your quick reply Andrew.

Where about would i put in this formula. Would it go in the cell formula or conditional formating box?

If the cell reads [anything.xls]Sheet1'!A1 does it go in there somehow. Dont think the number format will work as there is text, dates and times in the sheet.

 

[Andrew Poulsom]

The syntax is:

=IF(YourFormula,YourFormula,"")

To cater for text you can use:

=IF(LEN(YourFormula),YourFormula,"")

The number format I gave you was just an example. It's the presence of the second semicolon with nothing following it that suppresses the zero. So you can also use:

dd/mm/yyyy;;
hh:mm:ss;;

 

[Excelnoobster]

Ah. the ;; worked perfectly! Thanks, Andrew.