Hiding Returned Value of hlookup

[John D. Watker]

Okay, this is a tough one... (At least for me)

I'm using the hlookup formula to import the starting times and ending times
of employees on a daily assignment sheet. I'm compiling all the times onto
a weekly schedule where each individual's hours for the week are added up.
Now, I've done all this, but I get #NA when a particular employee is not on
a certain day. I'm currently using IF and ISERROR to convert the #NA to
"0", which allows the sheet to properly add up the hours being used by each
employee. Now, I need to be able to hide the zeros so I can print out a
half-way decent looking schedule.

I've gone into the tools menu to uncheck the 'display zeros' option, but
they still appear. Conditional formatting doesn't seem to work, and I don't
want to blot out the cells manually because the schedule changes a lot and I
don't want to hide an important change by accident. If I change the IF
value from "0" to something more pleasing, like a "-", I get an error
message from the weekly totals.

Any thoughts?


Thanks in advance,

John

 

[Dave Peterson]

Are you returning the string "0" (with the quotes) or the value 0?

I've never seen that option not work when the values are really 0.

Another couple of options:
format|conditional formatting and make the fill color equal the font color if
the value is 0.

Or use a custom format:
General;-General;;@
(positive;negative;0;text)

Or some variation of that.

 

[Pete_UK]

Instead of returning a zero if there is an error, you could return "" -
this should not affect any SUM formulae.

I'm surprised that conditional formatting does not work, though, as I
use the technique of setting the foreground colour to white if the cell
content is zero quite often.

Hope this helps.

Pete