Why do externally-linked cells appear as zero rather than null?
Posted by Ben O. on October 31, 2000 7:22 AM
My company's time sheet has an adjustments page where employees can make adjustments to hours they recorded on the previous pay period.
I use Data Validation > List so that the user can select a date from the previous pay period from a drop-down list, and VLOOKUP so that the hours he recorded for that day will be brought up in adjacent cells.
The dates and hours from the previous pay period are stored on the same spreadsheet, in hidden cells that contain links to the user's previous time sheet.
The problem is, cells that are completely empty, or null, on the linked time sheet, appear as zero values on the current time sheet. So any cells an employee didn't use will come up with a date of 1/00/1900 and 12:00 AM for his starting and ending times for that day.
I know how to hide zero values, but that doesn't solve the problem. My VLOOKUP formulas use range lookup, so if an employee selects a date to make an adjustment on, VLOOKUP will sometimes come up with 12:00 AM for his times rather than what he actually recorded.
My question is, how can I make it so that null cells on the linked worksheet are null cells on the destination worksheet?
-Ben