ISBLANK

[SharonDrumm]

I am trying to create a formula in a new cell that says if any of three other cells are not blank, then fill the contents of new cell with same text as in a different cell, otherwise don't put anything in the new cell.

I can't write the correct formula. What's more, I cannot figure out how to cycle through more than one set of the formulae in each cell. This is important because I don't want to leave a blank cell (and therefore in this instance, a blank row) for every instance when the argument is true (all are blank).

Any help is really appreciated. My skills are just not this developed, although I can conceive of what I want to do!

Sharon

 

[lenze]

Instead of ISBLANK, try using COUNT or COUNTA. Maybe something like =IF(COUNTA(ThreeCells)>0,CellAddress,"")
This message was edited by lenze on 2002-03-29 07:56

 

[Aladin Akyurek]

On 2002-03-29 07:49, SharonDrumm wrote:
I am trying to create a formula in a new cell that says if any of three other cells are not blank, then fill the contents of new cell with same text as in a different cell, otherwise don't put anything in the new cell.

I can't write the correct formula. What's more, I cannot figure out how to cycle through more than one set of the formulae in each cell. This is important because I don't want to leave a blank cell (and therefore in this instance, a blank row) for every instance when the argument is true (all are blank).

Any help is really appreciated. My skills are just not this developed, although I can conceive of what I want to do!

Sharon

Lets say that A1, B1, and C1 are the cells we want to test for being blank in D1 and E1 contains the relevant text:

=IF(OR(LEN(A1),LEN(B1),LEN(C1)),E1,"")

 

[Yogi Anand]

On 2002-03-29 07:49, SharonDrumm wrote:
I am trying to create a formula in a new cell that says if any of three other cells are not blank, then fill the contents of new cell with same text as in a different cell, otherwise don't put anything in the new cell.

I can't write the correct formula. What's more, I cannot figure out how to cycle through more than one set of the formulae in each cell. This is important because I don't want to leave a blank cell (and therefore in this instance, a blank row) for every instance when the argument is true (all are blank).

Any help is really appreciated. My skills are just not this developed, although I can conceive of what I want to do!

Sharon

Hi Sharon:
We need a little bit of more specificity here -- "when you say any of three other cells are not blank" -- Do you mean all three (each one of them is not blank), or any (any one of them is not blank).
Well anyway, here is a small example I have for you to consider:
A5=Great!
Cell C5=1
D5=2
E5=3
in cell F5 I am going to write a formula that checks whether all (each one of C5, D5, and E5) is blank -- if this condition is met, put in the value in cell A5 -- otherwise leave it blank

'=IF(AND(ISBLANK(C5),ISBLANK(D5),ISBLANK(E5)),A5,"")

HTH
Please post back if it works for you ... otherwise explain a little further and let us take it from there!

_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature
This message was edited by Yogi Anand on 2003-01-19 14:16

 

[wi_guy]

Assuming data is in cells a1,a2,a3, and cells to reference are a4 and a5


=IF(AND(ISBLANK(A1),ISBLANK(A2),ISBLANK(A3)),A4,A5)

 

[wi_guy]

Sheesh....you "MASTERS" are quick!!

 

[Aladin Akyurek]

Yogi & wi_guy,

Put in one of the target cells,

=IF(ZX,1,"") [ ZX must be an "untouched" cell ]

then try the ISBLANK formula you suggested.

Aladin

 

[Yogi Anand]

On 2002-03-29 08:17, Aladin Akyurek wrote:
Yogi & wi_guy,

Put in one of the target cells,

=IF(ZX,1,"") [ ZX must be an "untouched" cell ]

then try the ISBLANK formula you suggested.

Aladin

Hi Aladin:
I see your point that with the scenario you proposed, the affected target cell would appear to be blank -- but it really wouldn't be blank -- it will have a formula in it. So the formula that my cyber-buddy wi_guy and I proposed works as expected. I hope I am not missing some thing here!

 

[Aladin Akyurek]

On 2002-03-29 08:46, Yogi Anand wrote:

On 2002-03-29 08:17, Aladin Akyurek wrote:
Yogi & wi_guy,

Put in one of the target cells,

=IF(ZX,1,"") [ ZX must be an "untouched" cell ]

then try the ISBLANK formula you suggested.

Aladin

Hi Aladin:
I see your point that with the scenario you proposed, the affected target cell would appear to be blank -- but it really wouldn't be blank -- it will have a formula in it. So the formula that my cyber-buddy wi_guy and I proposed works as expected. I hope I am not missing some thing here!

In A1 enter:

=IF(ZX,1,"") [ ZX must be an "untouched" cell ]

B1 is a untouched cell (nothing in it)

C1 is a untouched cell (nothing in it)

In E1 enter: Great

In D1 enter:

=IF(AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)),E1,"")

Note. What Sharon wants is I think something different, but that's not the issue here.

What do you get?

Aladin

 

[SharonDrumm]

IF(AND(ISBLANK(C5),ISBLANK(D5),ISBLANK(E5)),A5,"")
HTH
Please post back if it works for you ... otherwise explain a little further and let us take it from there!

_________________
Yogi Anand
ANAND Enterprises
http://www.handtech.com/anand yogia@hotmail.com
This message was edited by Yogi Anand on 2002-03-29 08:07
[/quote]

Thank you! This worked. Now, the second part of my problem is not fixed. The idea is that on one worksheet, I have a checklist users will fill out. If certain criteria are met (some columns are checked), then I want that row value to appear on the next worksheet, where the user will go through another check-off exercise.

Using the formula you describe above, I can fill in the cells down one column, referencing the work done on the previous worksheet. Problem is, if none of the criteria are met for one cell, the whole row is left blank. Do you get my drift? If the criteria are met for only one observation on the previous worksheet, I could conceivably have 4 rows filled in interspersed with 20 blank rows on the new worksheet.

Any thoughts on this problem? Again, thanks for the formula!

Sharon
This message was edited by SharonDrumm on 2002-03-29 09:41