Very simple (you know how, and I dont)

[Stretlow]

I have a simple formula =E4+10 where the value in E4 is a date and populates F4 with 10 days after.... easy.




If the value in E is blank then F will say 10-jan i need this to be blank without using conditional formatting.


Ive used 3 conditional formatting conditions already.

is this possible?

 

[lenze]

First, Conditional Formatting would not work. You need an IF statement

=IF(E4,E4+10,"")

lenze

 

[Mike Blackman]

Hi,

Try;

=IF(E4="","",E4+10)

 

[mooseman]

=IF(E4,E4+10,"")

So just the cell reference in the IF statement means "IF CELL'S NOT EMPTY'......
I did not know that.... thanks lenze...

 

[lenze]

So just the cell reference in the IF statement means "IF CELL'S NOT EMPTY'......
I did not know that.... thanks lenze...

Not exactly True. It test for cells that are greater than 0, so text entered in the cell will produce an error. Since the OP said dates were being entered (hopefully with data validation in place), I choose the shortcut. Mike's suggestion is perhaps technically better.

lenze

 

[mooseman]

Thanks for the clarification.. It's good to know

 

[Stretlow]

Hi,

Try;

=IF(E4="","",E4+10)

Superb exactly what I needed.


Another quick one if I may.....

im using the conditional formatting to highlight an entire row ->

im using

=$F3=today() to highlight it if F3 hits todays date

ive set another conditional format up with

=$H3=today() to highlight in another colour when it hits this date


i know im nearly there but i want the first colour to highlight between condition 1 and 2's date

and the other colour when we hit condition 2 and thereafter.

Is this possible

Again help much appreciated

 

[im2bz2p345]

Not exactly True. It test for cells that are greater than 0, so text entered in the cell will produce an error. Since the OP said dates were being entered (hopefully with data validation in place), I choose the shortcut. Mike's suggestion is perhaps technically better.

lenze

Hello Lenze,

I'm not sure if this entire statement is necessarily true "It test for cells that are greater than 0, so text entered in the cell will produce an error." Please see example below:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 90px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">1/1/2009</TD><TD style="TEXT-ALIGN: right">1/11/2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">1/3/2009</TD><TD style="TEXT-ALIGN: right">1/13/2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">-1</TD><TD>True</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>TEST STRING</TD><TD>#VALUE!</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(A1,A1+10,"")</TD></TR><TR><TD>B2</TD><TD>=IF(A2,A2+10,"")</TD></TR><TR><TD>B3</TD><TD>=IF(A3,"True","False")</TD></TR><TR><TD>B4</TD><TD>=IF(A4,"True","False")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


I believe if you reference a cell (i.e. in the OP's case, E4).. it checks to see if there is a value present or not.

Please correct me if I'm wrong.

~ Busypee

 

[lenze]

You are correct, Busypee. I simply ignored the negatives on the assumption they would not come into play given the OP.
lenze

 

[Stretlow]

Any further tips from you good people?