Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for 2 true arguments and return true or false | Excel Discussion (Misc queries) | |||
Easy formula to return a true or false | Excel Worksheet Functions | |||
Return blank for true "if" statement when charting, not 0 | Charts and Charting in Excel | |||
Return blank cell if 'find' statement not true | Excel Worksheet Functions | |||
Function to return True/False if all are validated as True by ISNU | Excel Worksheet Functions |