Formula to return a true blank
 

Is there a way for a formula to return a true blank?

I'm trying to do a query, for either DCountA or SumProduct, that refers to
another cell for the criteria. I wish to have that criteria ignored if it is
blank.

=If(Isblank(j4),"",j4) -- doesn't do the job because "" actually returns an
empty string rather than a true blank. "" is not treated the same as a true
blank in criteria ranges.

The reason I'm trying to do this is that I'm trying to do several (actually
24) queries based upon selections from dropdown lists.

Formula to return a true blank
 

No, a formula cannot return a true blank, because the cell contains
the formula !!

Have you tried something like this:

=IF(j4="","",j4)

Hope this helps.

Pete

On Oct 30, 1:38 pm, dcwood57
wrote:
Is there a way for a formula to return a true blank?

I'm trying to do a query, for either DCountA or SumProduct, that refers to
another cell for the criteria. I wish to have that criteria ignored if it is
blank.

=If(Isblank(j4),"",j4) -- doesn't do the job because "" actually returns an
empty string rather than a true blank. "" is not treated the same as a true
blank in criteria ranges.

The reason I'm trying to do this is that I'm trying to do several (actually
24) queries based upon selections from dropdown lists.

Formula to return a true blank
 

try
=if(len(trim(j4))<1,"",j4)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dcwood57" wrote in message
...
Is there a way for a formula to return a true blank?

I'm trying to do a query, for either DCountA or SumProduct, that refers to
another cell for the criteria. I wish to have that criteria ignored if it
is
blank.

=If(Isblank(j4),"",j4) -- doesn't do the job because "" actually returns
an
empty string rather than a true blank. "" is not treated the same as a
true
blank in criteria ranges.

The reason I'm trying to do this is that I'm trying to do several
(actually
24) queries based upon selections from dropdown lists.

Formula to return a true blank
 

I think that this:

a formula cannot return a true blank, because the cell contains the formula !!

is the problem. It doesn't matter what formula I put in, the criteria range
will not be blank. I've been looking for a solution for this for quite a
while. I'm afraid that the solution is not to be found without programming.
I may write a VBA script to do what I'm trying to do. Or perhaps learn more
about Access Report writing as the database that I'm querying is an Access DB
anyway.

Thanks for your help.

Don


"Pete_UK" wrote:

No, a formula cannot return a true blank, because the cell contains
the formula !!

Have you tried something like this:

=IF(j4="","",j4)

Hope this helps.

Pete

On Oct 30, 1:38 pm, dcwood57
wrote:
Is there a way for a formula to return a true blank?

I'm trying to do a query, for either DCountA or SumProduct, that refers to
another cell for the criteria. I wish to have that criteria ignored if it is
blank.

=If(Isblank(j4),"",j4) -- doesn't do the job because "" actually returns an
empty string rather than a true blank. "" is not treated the same as a true
blank in criteria ranges.

The reason I'm trying to do this is that I'm trying to do several (actually
24) queries based upon selections from dropdown lists.

Formula to return a true blank
 

You're welcome - good luck with your macro.

Pete

On Oct 30, 10:07 pm, dcwood57
wrote:
I think that this:

a formula cannot return a true blank, because the cell contains the formula !!

is the problem. It doesn't matter what formula I put in, the criteria range
will not be blank. I've been looking for a solution for this for quite a
while. I'm afraid that the solution is not to be found without programming.
I may write a VBA script to do what I'm trying to do. Or perhaps learn more
about Access Report writing as the database that I'm querying is an Access DB
anyway.

Thanks for your help.

Don



"Pete_UK" wrote:
No, a formula cannot return a true blank, because the cell contains
the formula !!

Have you tried something like this:

=IF(j4="","",j4)

Hope this helps.

Pete

On Oct 30, 1:38 pm, dcwood57
wrote:
Is there a way for a formula to return a true blank?

I'm trying to do a query, for either DCountA or SumProduct, that refers to
another cell for the criteria. I wish to have that criteria ignored if it is
blank.

=If(Isblank(j4),"",j4) -- doesn't do the job because "" actually returns an
empty string rather than a true blank. "" is not treated the same as a true
blank in criteria ranges.

The reason I'm trying to do this is that I'm trying to do several (actually
24) queries based upon selections from dropdown lists. - Hide quoted text -

- Show quoted text -

Formula to return a true blank
 

Instead of ISBLANK(J4) use LEN(J4)=0.


On Oct 30, 6:07 pm, dcwood57
wrote:
I think that this:

a formula cannot return a true blank, because the cell contains the formula !!

is the problem. It doesn't matter what formula I put in, the criteria range
will not be blank. I've been looking for a solution for this for quite a
while. I'm afraid that the solution is not to be found without programming.
I may write a VBA script to do what I'm trying to do. Or perhaps learn more
about Access Report writing as the database that I'm querying is an Access DB
anyway.

Thanks for your help.

Don



"Pete_UK" wrote:
No, a formula cannot return a true blank, because the cell contains
the formula !!

Have you tried something like this:

=IF(j4="","",j4)

Hope this helps.

Pete

On Oct 30, 1:38 pm, dcwood57
wrote:
Is there a way for a formula to return a true blank?

I'm trying to do a query, for either DCountA or SumProduct, that refers to
another cell for the criteria. I wish to have that criteria ignored if it is
blank.

=If(Isblank(j4),"",j4) -- doesn't do the job because "" actually returns an
empty string rather than a true blank. "" is not treated the same as a true
blank in criteria ranges.

The reason I'm trying to do this is that I'm trying to do several (actually
24) queries based upon selections from dropdown lists. - Hide quoted text -

- Show quoted text -