How to catch an empty cell which has a formula 1

[BJ1106]

Hi,

How can I catch an empty cell? the cell has a formula which reference to another cell. I tried isblank(A1), didn't work; I tried trim(A1) = "", didn't work; I tried len(A1) = 0 (returend 1), didn't work.

Can anyone please help?

what i try to accomplish is if A1 is empty, B1 shows "---", else B1 shows 100%.

Please help.

 

[Fenrirshowl]

Have you tried the ISBLANK function?

 

[BJ1106]

Yes. I did try ISBLANK.

 

[Corgano]

It would help to know the formula in A1, does the formula return a blank?

If so, =if(A1="","---",1) should work... but if fully depends on what the formula in A1 is.

 

[SkipVought]

FYI...

if a cell has a formula it is NOT empty.

You might want to use the LEN function.

Skip,

_[red][/red]

 

[BJ1106]

well, the formula is very complicated.

for example, i am checking whether A1 is empty:

A1 = C1;

C1 = "Sheets!D1"

D1 = Sum(P1:Q10)"

I am just giving you one example of how the formula looks behind. And I am dealing with many cells like this situation, and each cell somehow link to a cell from a different spreadsheet.

 

[BJ1106]

I tried LEN function.

if A1 is empty, Len(A1) returns 1; If A1 has value 1, Len(A1) also returns 1.

 

[AnotherHiggins]

Please explain what it is you are trying to accomplish. You ask, "How can I catch an empty cell. What does that mean?

Do you want to write another formula that takes into account if, say, A1 is actually empty vs. returning a zero-length string (looks empty, but contains a formula that returns "")?

If so, IsBlank will work.

Try this as an example:
-Open a new workbook.
-In A1, type in this formula: [COLOR=blue white]=""[/color]
-In B1, type in this formula: [COLOR=blue white]=IsBlank(A1)[/color]
-In B2, type in this formula: [COLOR=blue white]=isblank(A2)[/color]

A1 and B1 will both appear blank
B1 will say False
B2 will say True

If this isn't what you are trying to do, please explain more clearly.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[BJ1106]

Here is what I am trying to do:

Col A Col B Col C
Row1 27 100%
Row2 ---
Row3 50 20 120%


Cells A1, A2, A3, B1, B2, B3 all have fomulas.

Situation 1: A1 has value, B1 is empty, C1 shows 100%;
Situation 2: A2 is empty, B2 is empty, C2 shows "---";
Situation 3: A3 has value, B3 has value, C3 = (A3-B3)/B3(show percentage).

I used ISBlank(B2), it returns FALSE. I tried TRIM(B2) = "", it returns FALSE. I tried Len(B2) = 0, Len(B2) returns 0.

the formulas for the cells in Col A and B look like this:
A1: =O1;
O1 = MASTER!B2
In the MASTER sheet, cell B2 also has a fomula, it's a sum of a range, like SUM(A1:C8.

 

[xlbo]

the problem is the SOURCE formula and what it returns

If A1=O1 then you need to test for what O1 returns - if the return value of O1 is " " then you will, of course get that returned to A1 as well. There is no point trying to see if A1 is blank as it cannot be - there is a formula there. What is the value in MASTER!B2 as this is the value which will trickle back to A1 - it is therefore the result of this formula that you need to evaluate

what is the formula in MASTER!B2 ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[BJ1106]

well,
Master!B2's formula is =SUM(W6:Y11). the each cell in this range has formula also, for example, W6's formula is "=O22", O22's formula is "SUM(C8:C21)".

 

[AnotherHiggins]

Maybe I'm being dense, but it seems to me that you still haven't told us what you're having trouble with!

In the outline you provided in your post dated 26 Sep 06 13:17, what is not doing what you want?

"I used ISBlank(B2), it returns FALSE". Of course it does - that cell is not blank, it contains a formula. But [attn]where[/attn] did you try that formula, and what do you want to do with it?

Also, how is (50-20)/20 120%? It should be 150%.

Please 'take a step back' and remember that we don't know what you're thinking; only what you write.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[DirkStruan]

Also, is there any (conditional) formatting set up?

If:

A1: =O1
O1 = MASTER!B2
MASTER!B2= SUM(A1:C8)

If A1:C8 on the master sheet are all empty or zero, then O1 and A1 on the current sheet should equal the value zero.

You could add the following macro to a module:

Sub whats_that()
MsgBox ActiveCell.Value
End Sub

then select the cell you are having trouble with evaluating and run the macro. What does the message box say?

 

[BJ1106]

Hi John,

I did mention what I am trying to do in my prior post.

the exactly formula i am using in a cell C1 is:

=IF(AND(ISBLANK(A1),ISBLANK(B1)),"---"),IF(AND(ISNumber(A1), ISBLANK(B1)),"100%",IF(AND(ISNUMB
ER(A1),ISNUMBER(B1)),(A1-B1)/B1,"")))

since A1, B1 have formula, the ISBLANK function didn't work. I tried to get some help to know how I can catch the "Empty" value. hope it's clear now.

Thanks.

 

[BJ1106]

***************************
Sub whats_that()
MsgBox ActiveCell.Value
End Sub
***************************

i ran the above code, the message box showing "0".

 

[DirkStruan]

If A1 is a reference to a SUM function in another cell and B1 is blank the following should work:

=IF(AND(A1=0,B1=0),"---"),IF(AND(ISNumber(A1), B1=0),"100%",IF(AND(ISNUMBER(A1),ISNUMBER(B1)),(A1-B1)/B1,"")))

Is that what you need?

D

 

[BJ1106]

I just tried when you before i saw your post, yes, it works perfectly. thank you very much.