MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Get formula to return Blank instead of Zero


Posted by chezlinds on January 29, 2002 9:16 AM

I have a workbook with several worksheets. I am using one of the sheets as a master to populate the others. My problem is that some of the cells in the master don't have any contents, but when I use the formula such as
=(MASTER!A1) to populate corresponding cells on other sheets if there's no value it comes up as 0 or (if the cell is formatted as a date) 1/0/00. Any help appreciated.

Lindsay


Posted by faster on January 29, 2002 9:28 AM


you may try Tools/Options/View/ and uncheck Zero Values

or this formula should work

=if((MASTER!A1)="","",(MASTER!A1))

Posted by David Gibson on January 29, 2002 9:33 AM

Lindsay,

You can use an if function in combination with the isblank() function in your formula to test each cell. Then if the cell is blank, simply have the formula enter two quotes, "", instead of 0.

=if(isblank(MASTER!A1),"",MASTER!A1)

This formula says, if MASTERA1 is blank, enter ""(which is a blank cell), else enter the value from MASTERA1.

Posted by chezlinds on January 29, 2002 10:04 AM

FANTASTIC! Thanks so much!

Posted by chezlinds on January 29, 2002 10:06 AM

I have already implemented the other formula, but thanks so much for your help!