Blank cells returning a 0

[eraust]

Hi All

I have a workbook which I am trying to link to another using INDEX MATCH.

The cells I am asking INDEX and MATCH to return have different values in them and some of them are blank. Some, but not all, of the blank cells are returning zeros rather than a blank cell.

I've tried to check the formatting of the cells to ensure they're all the same and they seem to be. I've also tried adding a conditional formatting rule using ISBLANK selecting the custom format category and entering a semicolon in the type field, but some of my cells are still returning zeros. Any ideas?

Thanks in advance

 

[eraust]

Ok I have just come across the 'Show zeros in blank cells' option in the advanced files option, so I have solved this.

That took me longer than it should have to find out and I was obviously very much overcomplicating things.

 

[eraust]

Hi All - bringing this back as I need a bit of help.

I've used the 'Show zeros in blank cells' option on one of my work sheets but turns out this option thinks that 00:00 (as in, midnight) is a zero and is now showing me blank cells there where I need it to say midnight. The 00:00 cells are formatted Custom, hh:mm and I can't change them to 24:00 as some of my formulas involve time calculations.

Any ideas? Thanks in advance.

 

[GuzmanServices]

Without a screenshot, it appears an IF statement might be needed. Let me know if you can incorporate the following into the 1st Cell in your formulas (assuming Column headers), where B2 refers to the first Cell in Columns with 0. If this doesn't work, please add a screenshot. Thanks!
=IF(B2=0,"midnight",B2)

 

[pjmorris]

another thought. Technically 00:00 is not 24:00 as 24:00 doesn't exist. The last second of a day is 23:59:59, one second later the time is 00:00:00, but the day (an integer as far as Excel is concerned) has incremented by 1. If this is important (it presumably is!) you need to consider the date component of the cells and you can identify this by using 'd hh:mm' as the custom format.