Cell Formatting Issue -Sheet Agreement When Sorted

Hi Everyone,

I am trying to reference a cell in sheet All Employees in sheet Brock. This is an employee training database.

Currently there is no data in the cell I want to reference (B9).
When I use the formula ='all employees'!b9 the cell in the brock sheet shows "january 00 00" even when the referenced cell is empty. This problem is eliminated if I formate the Brock cell to General. It then displays a zero.

However, the cell in the All Employees Sheet is going to be a date. If I format the Brock cell to general, when I enter a date in All Employees, it gives a large number, not a date. This problem is eliminated by changing the formating of the Brock cell to Date.

What I would like to do is be able to type the date in the All Employees sheet and have it magically transfer to the Brock sheet without any additional work. I would like the brock sheet to be printable, and with January 00 00 written all over it, not only is it messy but its incorrect.

Can anyone please offer me some help?

Thank you!
Rosa