display substitution?

[Mike]

I have a large spreadsheet which references cells from other spreadsheets.
There are hundreds of dates on the spreadsheet that are reported as
0-Jan-1900 because they are superseded or unused in the individual sheets. Is
there a way in which I can insert a command to display nothing if
date=0-Jan-1900, so that only relevant material is presented?

Thank you!

 

[Pete_UK]

You could have something like:

=IF(ref_cell="","",your_existing_formula)

Alternatively, you could use conditional formatting such that if the
cell content is zero then use white foreground colour (which then
looks blank on a white background).

Hope this helps.

Pete

 

[ShaneDevenshire]

There are a number of ways depending on what is actually in the cell.

1. Try Tools, Options, View, and turn off Zero.

2. You could also apply conditional formatting
Format, Conditional Formatting, Equal to, 0 and then set the Font Color to
White.

 

[Mike]

Thank you, Pete!

You could have something like:

=IF(ref_cell="","",your_existing_formula)

Alternatively, you could use conditional formatting such that if the
cell content is zero then use white foreground colour (which then
looks blank on a white background).

Hope this helps.

Pete

 

[Mike]

Thank you, Shane!

One thing though, I ahve seen a lot of people referring to Tools > Options.
In Office 2007, there isn't a Tools menu anymore. To what are you referring,
please?

Thanks again.

Mike

 

[Bob I]

Office Button, Excel Options, Advanced, Display Options section.

 

[Mike]

Thanks, Bob, so it is that people are still using the old labels to describe
the same process more or less?

 

[Bob I]

This newsgroup gets questions and answers from users of darn near every
version of Excel that's been released. So, it is a case of people not
mentioning what version they are using when they ask a question, so you
get an answer based on what the user has who replies.

HTH

 

[Pete_UK]

You're welcome, Mike - thanks for feeding back.

Pete