ATTENTION: You are viewing a page formatted for mobile devices; to view the full web page, click HERE.

Main Area and Open Discussion > General Software Discussion

Test cell value for BLANK in Excel and take action (or not) based on it

(1/2) > >>

questorfla:
This is actually pretty simple.
Columns Cx and Dx both have an auto calculation function that I need to lock to protect the formula from being changed.
The calculation is based on the contents of column Ax and column Bx.
The problem is that due to auto calculating, the values of column Cx and Dx are instantly filled in based on calculating a value from BLANK in both Ax and Bx

I need to add a test both C and D calculations that check for Blank values in either Ax or Bx and do nothing if either are blank.
Then Cx and Dx will also be blank.
As it is, the auto calculation runs and puts a value into Cx and Dx based on a value of BLANK in Ax and Bx.  It is just a nuisance but one that is annoying.
I need to find a way to incorporate this into my current setup such that it will allow the current formulas to run if those cells are filed in with anything other than just blank

Target:
there are probably dozens of ways to do this, but without knowing more about your particular case you can try this (obviously this is for cell Cx)

=if(aX = "","",XXXX)

IainB:
there are probably dozens of ways to do this, but without knowing more about your particular case you can try this (obviously this is for cell Cx)
=if(aX = "","",XXXX)
______________________
-Target (September 03, 2015, 12:35 AM)
--- End quote ---
Yes, but bear in mind that:

* "" = NULL (which is neither a text character nor a numeric value).
* " " = SPACE (which is a text character).
* 0 = a numeric value of zero.
You could also consider using this (from Excel Help): the ISBLANK function - Returns TRUE if the value is blank.

TaoPhoenix:
there are probably dozens of ways to do this, but without knowing more about your particular case you can try this (obviously this is for cell Cx)
=if(aX = "","",XXXX)
______________________
-Target (September 03, 2015, 12:35 AM)
--- End quote ---
Yes, but bear in mind that:

* "" = NULL (which is neither a text character nor a numeric value.
* " " = SPACE (which is a text character).
* 0 = a numeric value of zero.
You could also consider using this (from Excel Help): the ISBLANK function - Returns TRUE if the value is blank.
-IainB (September 05, 2015, 06:57 AM)
--- End quote ---

Actually Iain you could be on to a nice little tip for him because Nulls and ISBLANK could fail out if he somehow hit space and it got in there. So he could write it as a compound effect to check for two or three of those cases.

IainB:
Actually Iain you could be on to a nice little tip for him because Nulls and ISBLANK could fail out if he somehow hit space and it got in there. So he could write it as a compound effect to check for two or three of those cases.
_________________
-TaoPhoenix (September 05, 2015, 07:54 AM)
--- End quote ---
Yes, I realise that is a possibility, however, as @Target put it: