Conditional formatting with linked value

[wiersma7]

I'm Paste Special/Linking formatted dates (dddd, mmmm dd, yyyy) and text
values in a calendar type list - linking to a copy of the calendar. When I
don't have any dates or values on specific lines, I want the copy calendar
to have blank lines. However, because I format the date I get a '0' in the
'blank' line. Or if I don't have a date on the line I get 'Sunday, January
0, 2000' in the copy calendar line. I've been trying to use the conditional
formatting (I use Excel 2007), using white as the color for the lines I
don't want to show, but can't seem to find the right combination. I've
tried a combination of rules as well, but I'm not coming up with anything
that works. Do you have a suggestion?

Thanks much,
wiersma7

 

[Gord Dibben]

Using Special Paste>Links you do not get any error trapping for blank cells.

You have to build them from scratch.

=IF(A1="","",A1)

Between sheets =IF(Sheet1!A1="",""Sheet1!A1)


Gord Dibben MS Excel MVP

 

[Tyro]

Dates in Excel are numbers. Excel's date range is Jan. 1, 1900, day 1
through Dec. 31, 9999 day 2,958,465. There is also a fictitious date of Jan.
0, 1900, day 0
When you format numbers in that range as a date, you see a date. Those
"dates" are just numbers and can be treated as numbers. The formatting does
not change the numbers. Formatting is for human consumption. To see the date
serial number, press Ctrl+Accent grave, the key above the Tab key and to the
left of the 1 (one) key. Press again to toggle back. Many people confuse the
formatting of the contents of a cell with the contents of the cell. For
example, I can have 355 pennies on the table in front of me and say I have
355 pennies or I have $3.55. The $3.55 is formatting. Yet I have still only
355 pennies. Treat your dates as you would numbers and format appropriately.

Tyro

 

[OssieMac]

Further to Tyro's comments, if the date values are Zero, you can turn off
displaying zero values in Options. See 'Display or Hide zero values' in Help.