Cell Formatting Issue -Sheet Agreement When Sorted

**rosaf** · 05-29-2009, 11:31 AM

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

**DonkeyOte** · 05-29-2009, 11:55 AM

You should find you can use a Custom Format as follows: mmmm dd yy;;

**rosaf** · 05-29-2009, 12:05 PM

I think that this is working, thank you so much.
If you have a moment, could you explain why that would work so that if I have a similar issue I can fix it?
What do the ;; 's do?

Thanks!

**DonkeyOte** · 05-29-2009, 12:13 PM

A custom number format can basically be broken into 3 parts (based on SIGN):

Positive Number Format ; Negative Number Format ; 0 Number Format

Dates in XL are essentially Integers > 0 ... so by specifying a format for -ve and 0 (but leaving format as blank) 0 value is essentially displayed as a blank... only a valid date value will display as a date.

There is another approach for Custom Formatting 0, ie:

[=0]"";mmmm dd yy

I tend to use the latter approach out of habit but the first approach outlined is the more common.
The latter approach is better if the criteria is something other than 0 (ie format the number 5 a certain way) - as this can't be done using the positive/negative/0 approach.

**rosaf** · 05-29-2009, 12:18 PM

Amazing, thank you so much!

**rosaf** · 05-29-2009, 03:43 PM

This solution was very helpful to me!

I now have the issue that when I sort the data in All Employees, the Brock sheet does not follow suit.

For example: I add a name to the bottom of the all employee list, then add the data, and agree it to the brock sheet
Now I want to make that All Employee sheet sorted a-z. When I do so, the data in the Brock sheet changes.

Anything I can do?

Thanks!

**DonkeyOte** · 05-30-2009, 06:40 AM

I think you should provide a sample of your file (dummy data if otherwise confidential) and outline what's happening - it's hard to follow / visualise (for me at least)

**rosaf** · 06-01-2009, 09:22 AM

Here is a dummy sheet, thank you for suggesting that DonkeyOte.

If I try to sort the All Employee Sheet by Name, then the Company 1 Sheet Data no longer matches the correct employee.

Its almost as though I need to link the data across row 2 (for example) so that when the names move, so do the dates.
Any help you can give would be fantastic.

Thank you!

**rosaf** · 06-01-2009, 10:30 AM

Strange, in the dummy sheet its working, in my database its not...

**DonkeyOte** · 06-01-2009, 11:04 AM

If you're not pulling back all records onto Company1 sheet and you wish to alter the All Ees sheet then you will need to use Functions to retrieve the info to Company 1 based on name specified in A, in particular I would draw your attention to the following functions which you can investigate further in XL Help files, they are:

VLOOKUP
INDEX
MATCH

You may find VLOOKUP the simplest to follow initially.