MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by JAF on October 31, 2000 8:21 AM

Ben

You can use either of the 2 formulas when creating the links to the external source:

=if(destination="","",destination)

or

=if(isblank(destination),"",destination)


JAF

Posted by Ben O. on October 31, 2000 11:37 AM

Thanks, JAF. I was hoping I wouldn't have to do that since the link names are pretty long. But I guess that's the only way! I appreciate the help.

-Ben

Posted by John Bingham on November 02, 2000 1:08 PM

-Ben

Perhaps you could create a new column of his suggested formula referencing his existing links, then hide your column of old links.