Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
display substitution?
You're welcome, Mike - thanks for feeding back.
Pete On Aug 26, 6:35*pm, Mike wrote: Thank you, Pete! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More than i substitution | Excel Discussion (Misc queries) | |||
Substitution of words | Excel Discussion (Misc queries) | |||
Substitution | Excel Discussion (Misc queries) | |||
substitution | Excel Discussion (Misc queries) | |||
Substitution | Excel Discussion (Misc queries) |