display substitution?
 

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!

display substitution?
 

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

On Aug 26, 3:07*pm, Mike wrote:
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!

display substitution?
 

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.

--
Cheers,
Shane Devenshire


"Mike" wrote:

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!

display substitution?
 

Thank you, Pete!

"Pete_UK" wrote:

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

On Aug 26, 3:07 pm, Mike wrote:
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!

display substitution?
 

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

"ShaneDevenshire" wrote:

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.

--
Cheers,
Shane Devenshire


"Mike" wrote:

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!

display substitution?
 

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

Mike wrote:

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

"ShaneDevenshire" wrote:


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.

--
Cheers,
Shane Devenshire


"Mike" wrote:


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!

display substitution?
 

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

"Bob I" wrote:

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

Mike wrote:

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

"ShaneDevenshire" wrote:


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.

--
Cheers,
Shane Devenshire


"Mike" wrote:


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!

display substitution?
 

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

Mike wrote:

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

"Bob I" wrote:


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

Mike wrote:


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

"ShaneDevenshire" wrote:



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.

--
Cheers,
Shane Devenshire


"Mike" wrote:



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!

display substitution?
 

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

Pete

On Aug 26, 6:35*pm, Mike wrote:
Thank you, Pete!