Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Leave a cell COMPLETELY blank if there is an error
My question is similar to many others in this discussion group, but I have
searched pretty thoroughly and not found the answer to my exact question... What I want to do is to leave a cell completely blank if the result of an ISERROR test (shown below) is true. I need that cell to be completely blank, because in another cell I have an INDIRECT formula that relies on the COUNTA function that refers to this first cell. If the IF(ISERROR...) formula deposits a "" in a cell, it will be counted in the COUNTA formula, and INDIRECT will pull the (nonexistent) value from this cell. (This formula is on a sheet named calc) =IF(ISERROR((raw!K285),"",(raw!J285-(raw!K285/raw!K273-1)*100)) =INDIRECT(CONCATENATE("'calc'!f",COUNTA(calc!$F:$F )+23)) Is there a way to do this? Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Leave a cell COMPLETELY blank if there is an error
We've been asking for some kind of BLANK() or NULL() worksheet function for
some time. Haven't gotten it, so no, there's no way to do this. Unless of course you want to use VBA to clear the cells with errors, and then if the data changes, you need to repopulate the cells with formulas. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "hello" wrote in message ... My question is similar to many others in this discussion group, but I have searched pretty thoroughly and not found the answer to my exact question... What I want to do is to leave a cell completely blank if the result of an ISERROR test (shown below) is true. I need that cell to be completely blank, because in another cell I have an INDIRECT formula that relies on the COUNTA function that refers to this first cell. If the IF(ISERROR...) formula deposits a "" in a cell, it will be counted in the COUNTA formula, and INDIRECT will pull the (nonexistent) value from this cell. (This formula is on a sheet named calc) =IF(ISERROR((raw!K285),"",(raw!J285-(raw!K285/raw!K273-1)*100)) =INDIRECT(CONCATENATE("'calc'!f",COUNTA(calc!$F:$F )+23)) Is there a way to do this? Thank you! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Leave a cell COMPLETELY blank if there is an error
If you don't want to count the pseudo-blank, why not use COUNT() rather than
COUNTA()? -- Gary''s Student - gsnu200757 "hello" wrote: My question is similar to many others in this discussion group, but I have searched pretty thoroughly and not found the answer to my exact question... What I want to do is to leave a cell completely blank if the result of an ISERROR test (shown below) is true. I need that cell to be completely blank, because in another cell I have an INDIRECT formula that relies on the COUNTA function that refers to this first cell. If the IF(ISERROR...) formula deposits a "" in a cell, it will be counted in the COUNTA formula, and INDIRECT will pull the (nonexistent) value from this cell. (This formula is on a sheet named calc) =IF(ISERROR((raw!K285),"",(raw!J285-(raw!K285/raw!K273-1)*100)) =INDIRECT(CONCATENATE("'calc'!f",COUNTA(calc!$F:$F )+23)) Is there a way to do this? Thank you! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Leave a cell COMPLETELY blank if there is an error
Thanks Jon and Gary's Student -- the reason for not using COUNT() is that I
also have text in the column, which does not counted, but it is easier to work around that so I will switch. Would be nice to have something that would leave a cell totally blank. "Gary''s Student" wrote: If you don't want to count the pseudo-blank, why not use COUNT() rather than COUNTA()? -- Gary''s Student - gsnu200757 "hello" wrote: My question is similar to many others in this discussion group, but I have searched pretty thoroughly and not found the answer to my exact question... What I want to do is to leave a cell completely blank if the result of an ISERROR test (shown below) is true. I need that cell to be completely blank, because in another cell I have an INDIRECT formula that relies on the COUNTA function that refers to this first cell. If the IF(ISERROR...) formula deposits a "" in a cell, it will be counted in the COUNTA formula, and INDIRECT will pull the (nonexistent) value from this cell. (This formula is on a sheet named calc) =IF(ISERROR((raw!K285),"",(raw!J285-(raw!K285/raw!K273-1)*100)) =INDIRECT(CONCATENATE("'calc'!f",COUNTA(calc!$F:$F )+23)) Is there a way to do this? Thank you! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Leave a cell COMPLETELY blank if there is an error
If you have Text in some cells, then a different approach is needed:
In place of: COUNTA(calc!$F:$F) use: 65536-COUNTBLANK(calc!$F:$F) -- Gary''s Student - gsnu200757 "hello" wrote: Thanks Jon and Gary's Student -- the reason for not using COUNT() is that I also have text in the column, which does not counted, but it is easier to work around that so I will switch. Would be nice to have something that would leave a cell totally blank. "Gary''s Student" wrote: If you don't want to count the pseudo-blank, why not use COUNT() rather than COUNTA()? -- Gary''s Student - gsnu200757 "hello" wrote: My question is similar to many others in this discussion group, but I have searched pretty thoroughly and not found the answer to my exact question... What I want to do is to leave a cell completely blank if the result of an ISERROR test (shown below) is true. I need that cell to be completely blank, because in another cell I have an INDIRECT formula that relies on the COUNTA function that refers to this first cell. If the IF(ISERROR...) formula deposits a "" in a cell, it will be counted in the COUNTA formula, and INDIRECT will pull the (nonexistent) value from this cell. (This formula is on a sheet named calc) =IF(ISERROR((raw!K285),"",(raw!J285-(raw!K285/raw!K273-1)*100)) =INDIRECT(CONCATENATE("'calc'!f",COUNTA(calc!$F:$F )+23)) Is there a way to do this? Thank you! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Leave a cell COMPLETELY blank if there is an error
I was going to say that "" isn't blank, and in fact, ISBLANK(A1) returns
FALSE if A1 contains "". However, COUNTBLANK(A1) returns 1 whether A1 contains "" or whether A1 is completely blank. Nice to know. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gary''s Student" wrote in message ... If you have Text in some cells, then a different approach is needed: In place of: COUNTA(calc!$F:$F) use: 65536-COUNTBLANK(calc!$F:$F) -- Gary''s Student - gsnu200757 "hello" wrote: Thanks Jon and Gary's Student -- the reason for not using COUNT() is that I also have text in the column, which does not counted, but it is easier to work around that so I will switch. Would be nice to have something that would leave a cell totally blank. "Gary''s Student" wrote: If you don't want to count the pseudo-blank, why not use COUNT() rather than COUNTA()? -- Gary''s Student - gsnu200757 "hello" wrote: My question is similar to many others in this discussion group, but I have searched pretty thoroughly and not found the answer to my exact question... What I want to do is to leave a cell completely blank if the result of an ISERROR test (shown below) is true. I need that cell to be completely blank, because in another cell I have an INDIRECT formula that relies on the COUNTA function that refers to this first cell. If the IF(ISERROR...) formula deposits a "" in a cell, it will be counted in the COUNTA formula, and INDIRECT will pull the (nonexistent) value from this cell. (This formula is on a sheet named calc) =IF(ISERROR((raw!K285),"",(raw!J285-(raw!K285/raw!K273-1)*100)) =INDIRECT(CONCATENATE("'calc'!f",COUNTA(calc!$F:$F )+23)) Is there a way to do this? Thank you! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Leave a cell COMPLETELY blank if there is an error
The fact that ISBLANK() and COUNTBLANK() treat functions differently allow us
to count either way. -- Gary''s Student - gsnu200757 "Jon Peltier" wrote: I was going to say that "" isn't blank, and in fact, ISBLANK(A1) returns FALSE if A1 contains "". However, COUNTBLANK(A1) returns 1 whether A1 contains "" or whether A1 is completely blank. Nice to know. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gary''s Student" wrote in message ... If you have Text in some cells, then a different approach is needed: In place of: COUNTA(calc!$F:$F) use: 65536-COUNTBLANK(calc!$F:$F) -- Gary''s Student - gsnu200757 "hello" wrote: Thanks Jon and Gary's Student -- the reason for not using COUNT() is that I also have text in the column, which does not counted, but it is easier to work around that so I will switch. Would be nice to have something that would leave a cell totally blank. "Gary''s Student" wrote: If you don't want to count the pseudo-blank, why not use COUNT() rather than COUNTA()? -- Gary''s Student - gsnu200757 "hello" wrote: My question is similar to many others in this discussion group, but I have searched pretty thoroughly and not found the answer to my exact question... What I want to do is to leave a cell completely blank if the result of an ISERROR test (shown below) is true. I need that cell to be completely blank, because in another cell I have an INDIRECT formula that relies on the COUNTA function that refers to this first cell. If the IF(ISERROR...) formula deposits a "" in a cell, it will be counted in the COUNTA formula, and INDIRECT will pull the (nonexistent) value from this cell. (This formula is on a sheet named calc) =IF(ISERROR((raw!K285),"",(raw!J285-(raw!K285/raw!K273-1)*100)) =INDIRECT(CONCATENATE("'calc'!f",COUNTA(calc!$F:$F )+23)) Is there a way to do this? Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To leave the cell Blank | Excel Worksheet Functions | |||
Leave cell blank | Excel Worksheet Functions | |||
Leave Cell Blank if value is an error, below 0 or above 80 | Excel Discussion (Misc queries) | |||
leave a cell blank | Excel Worksheet Functions | |||
To Leave Cell blank Please | Excel Worksheet Functions |