I’ve created a fairly simple Excel workbook in which there are 10 data entry sheets (containing date, service, and initials) and a master sheet (containing the exact info on all of the other sheets, but compiled into one sheet). The master updates automatically when any data is entered on the other 10.
What has me stumped is when there are blank date cells on the entry sheets, it shows as 1/0/1900 on the master, and I can’t figure out how to get rid of that. I want it to show blank on the master when there are no dates entered on the entry sheets, but I can’t seem to find how to do that. (Dates and other info will be entered in the future.)
Also, other empty cells on the entry sheets show up as 0 on the master. I’d like these to be blank when there is no data on the data entry sheets, also.
So how do I do this?
I suspect the master sheet has entries like
=Sheet2!B6
meaning that the entry from Sheet2 cell B6 is reproduced in the relevant cell on the master sheet. The = sign treats a blank cell as a 0. (A date of zero is treated as Jan 0, 1900.)
If instead you use on your master sheet entries like
=IF(Sheet2!B6="","",Sheet2!B6)
those are double quotes " with no space between them
It will put blanks (empty quotes) on the master sheet if the the entry sheet is blank otherwise it will put the same entry there.
That’s one way to make it look right, but there is a big difference between “” and 0 for any averages or such.
A better tehcnique is to take the cells in teh master and change the format from “mm/dd/yy” (or whatever date format yuo use) to “mm/dd/yy;;”
That extra stuff on the end tells Excel how to format negative & zero values. by leaving both formats blank, that means format them as, well, blank. So they are invisible to look at but still have the value of 0.
Thanks, guys! I also appreciate the two different ways of attacking the problem. I’ve sent the sheet now, and I’ll know in a day or so if it’s acceptable to the client.