IF (working with blank cells)

  • Thread starter Thread starter bonejn
  • Start date Start date
B

bonejn

I have a column in sheet(1) which is set to have a numeric datatype.
This column may or may not have an entry
(some entries may be a value of zero).

In sheet(2) I'm using a cell reference to this column.
I'm using the formula =IF(ISBLANK(cellref),"",cellref) but
this creates a problem in that it no longer is a numeric datatype.
In otherwords, I need the cell in sheet(2) to contain
either a value, zero, or remain blank depending on what
is in the referenced cell in sheet(1).
Reason being is, if the cells in sheet(2) contain both
numerals and text then the Access Import Wizard will
choose the text datatype for this field.
I'm finding this a problem with both my numeric and date fields.

Is there another way to use an IF statement where it will do nothing if TRUE?

-B
 
I have a column in sheet(1) which is set to have a numeric datatype.
This column may or may not have an entry
(some entries may be a value of zero).

In sheet(2) I'm using a cell reference to this column.
I'm using the formula =IF(ISBLANK(cellref),"",cellref) but
this creates a problem in that it no longer is a numeric datatype.
In otherwords, I need the cell in sheet(2) to contain
either a value, zero, or remain blank depending on what
is in the referenced cell in sheet(1).
Reason being is, if the cells in sheet(2) contain both
numerals and text then the Access Import Wizard will
choose the text datatype for this field.
I'm finding this a problem with both my numeric and date fields.

Is there another way to use an IF statement where it will do nothing if TRUE?

-B
Click to expand...

I think you have misunderstood what formatting is all about. When you say
"set to have a numeric datatype", I assume you mean you have formatted as a
number. This is not the same as requiring a number, in the sense that some
programs define a datatype. You can still put text, for example, into such a
cell. All that formatting does is to define how the data will be displayed.

You could consider using a formula such as
=IF(ISNUMBER(Sheet1!A1),Sheet1!A1,0)
Then you will always get a number in Sheet2.
 
-B

I don't know how to solve your problem, but I can tell you that if you have an IF statement the cell will no longer be considered blank, even using the "". This also messes up sorting by bringing these psuedo-blank cells to the top instead of the bottom

I assume that you can't have the IF return a zero as this would mess up something else

Regards
Mark Graesse
(e-mail address removed)

----- (e-mail address removed) wrote: ----

I have a column in sheet(1) which is set to have a numeric datatype
This column may or may not have an entr
(some entries may be a value of zero)

In sheet(2) I'm using a cell reference to this column
I'm using the formula =IF(ISBLANK(cellref),"",cellref) bu
this creates a problem in that it no longer is a numeric datatype
In otherwords, I need the cell in sheet(2) to contai
either a value, zero, or remain blank depending on wha
is in the referenced cell in sheet(1)
Reason being is, if the cells in sheet(2) contain bot
numerals and text then the Access Import Wizard wil
choose the text datatype for this field
I'm finding this a problem with both my numeric and date fields

Is there another way to use an IF statement where it will do nothing if TRUE

-
 
Thanks Paul,

=IF(ISNUMBER(sheet1!A1),Sheet1!A1,0) still leaves me
with a value of zero even if the cell it's looking at is empty.

The formula needs to do this...

IF Sheet1!A1 is empty then Sheet2!A1 is empty
IF Sheet1!A1 is 0 then Sheet2!A1 is 0
IF Sheet1!A1 is some number then Sheet2!A1 is that same number

Unfortunately the IF statement when used in a formula doesn't
give me the option to leave a cell empty.

However, I found I can accomplish this using a vba sub routine (not a custom function).
It does add a level of complexity to the workbooks that I was not counting on but, oh well.
I won't post here since I'm not sure its appropriate to be posting vba code in this forum.

thanks again for your reply,
-Brad
 
=IF(ISNUMBER(sheet1!A1),Sheet1!A1,0) still leaves me
with a value of zero even if the cell it's looking at is empty.

The formula needs to do this...

IF Sheet1!A1 is empty then Sheet2!A1 is empty
IF Sheet1!A1 is 0 then Sheet2!A1 is 0
IF Sheet1!A1 is some number then Sheet2!A1 is that same number

Unfortunately the IF statement when used in a formula doesn't
give me the option to leave a cell empty.
Click to expand...
...

The closest you'll get is

=IF(AND(ISNUMBER(Sheet1!A1),NOT(ISBLANK(Sheet1!A1))),A1,"")

If you then have to ensure that the formulas appearing blank by evaluating to ""
are deleted, you'd be better off using a Calculate event handler to search
through the range of formulas deleting those cells that evaluate to text strings
(""). You might also want to add a Change event handler to Sheet1 so that when
entries are made therein, the formula above is copied into the entire range
where it's potentially needed, then let that worksheet's Calculate event handler
eliminate the ones evaluating to "". That'd be close to automating, but there
could be noticeable processing drag after each entry in Sheet1.
 
Back
Top