Help with Excel formula in a Workbook

S

Solograndma

Solograndma back with another complicated formula problem.
In my workbook I have a data entry sheet that transfers data to a printable
sheet. I have a copy and paste function with mid-range formulas for and
address as well as a manual entry area. I have the formula to use the pasted
information if it's there or the manually entered information if it is there.
What I need is for the cells in the printable page to be blank if both the
pasted and the manual entry areas are blank. Everything I have tried brings
back zeros and unfortunately this form nees to be able to be printed as a
blank form for hand written use. I truly appreciate any help available. This
message board has been great so far. Thanks.
 
D

Dave F

To test for two cells being blank, do something like the following:
=IF(ISBLANK(A1:A2),"blank","not blank")

This is an array formula and needs to be entered with CTRL + SHIFT + ENTER

Dave
 
S

Solograndma

Thanks Dave F, but this doesn't work with my current formula, which is:

=if('65-5300 Data Entry'!f40<>"",'65-5300 Data Entry'!f40,if('65-5300 Data
Entry'!j40<>" ",'65-5300 Data Entry'!j40))

From here I need it to say, however, if cells f40 and j40 in 65-5300 Data
Entry are both blank then this cell should be blank. It currently returns
"zeros".
 
F

Fred Smith

If you need to test for two separate cells to be blank, just do it with the AND
function, as in:

=if(and(a1="",b1=""),"both blank","at least one not blank")

Also, to test for both a blank and a space, use the Trim function, as in:

=if(trim(a1)="",true,false)

I don't know how your formula comes up with "zeros" unless you tell us what the
"zeros" are. Is it the actual text "zeros"? The number 0? More than one digit of
zeros? One possibility is that you don't define what should happen if your
second If statement is false.
 
D

David Biddulph

Fot f40 you've tested for "" (a string of zero length), but for j40 you've
looked for " " ( a single space).

Also, you haven't told the formula what to do if the 2 conditions aren't
met; presumably you want ...!j40,"")) if you want to return a blank?
 
S

Solograndma

The formula returns the data that is in each cell, but returns the number
zero when both of the targeted cells are blank. Based on the formula I
provided, should the formula's you provided be entered before or after what
is already there? Or in place of?
 
S

Solograndma

David Biddulph said:
Fot f40 you've tested for "" (a string of zero length), but for j40 you've
looked for " " ( a single space).

Also, you haven't told the formula what to do if the 2 conditions aren't
met; presumably you want ...!j40,"")) if you want to return a blank?
Click to expand...
 
S

Solograndma

I have tried f40 with " " along with j40 with " ", I have tried f40 with ""
along with j40 with "". Both return the number zero when the targeted cells
are blank. What I am asking is how do I tell the formula what to do if the 2
conditions are not met. That is the part I can't figure out. Believe me, I
have tried many many combinations and operands.
 
S

Solograndma

Okay, I had an epiphany, FINALLY. Your statement "presumably you want
....!j40,""))" is what worked. I was trying to put the ,"" in the wrong place.

Thank you for your help.
 
Top