As you can see by the attached worksheet - I want to copy certain data from "database" sheet to the "summary" sheet. I think I have it set up correctly. The only think I can't figure out is - how do I hide the formula in the summary sheet so it does not show until there is data to copy over?
Solved: Excel-ignore blank cells in formula
- Status
1 - 14 of 14 Posts
Joined
·
2,278 Posts
Very quickly
You could use as an example in cell A18 in the Summary sheet
=IF(ISBLANK(Database!A6)," ",Database!A6)
However you need to be careful that you are trapping all errors or occurences that may occur in a cell. Such as
ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)
Value is the value you want tested. Value can be a blank (empty cell), error, logical, text, number, or reference value, or a name referring to any of these, that you want to test.
Each one of the above IS functions, returens True or False and you use the If statement to decide what will happen if what you are looking for if True or False.
If you are not familiar with using If statements, then you need to read up on those and get into nested IF statements
You could use as an example in cell A18 in the Summary sheet
=IF(ISBLANK(Database!A6)," ",Database!A6)
However you need to be careful that you are trapping all errors or occurences that may occur in a cell. Such as
ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)
Value is the value you want tested. Value can be a blank (empty cell), error, logical, text, number, or reference value, or a name referring to any of these, that you want to test.
Each one of the above IS functions, returens True or False and you use the If statement to decide what will happen if what you are looking for if True or False.
If you are not familiar with using If statements, then you need to read up on those and get into nested IF statements
Joined
·
2,278 Posts
Bombs is a very good one if all you want to do is hide results when there is no data to feed off and is preferred
You use IF and ISblank when you are testing the data in cells, before you do the calculation.
You use IF and ISblank when you are testing the data in cells, before you do the calculation.
Joined
·
2,278 Posts
Same to you Bomb
Well lets just hope we can beat Manu in the next 2 matches to appease.
Well lets just hope we can beat Manu in the next 2 matches to appease.
Joined
·
2,278 Posts
When I said Bombs, I meant the suggestion by Bomb #21
Tools > Options > View, then uncheck "Zero values".
Did you do that one before you did the ISBLANK formula?
Maybe you could upload your new spreadsheet that appears to have lost all the data.
Tools > Options > View, then uncheck "Zero values".
Did you do that one before you did the ISBLANK formula?
Maybe you could upload your new spreadsheet that appears to have lost all the data.
Joined
·
2,278 Posts
Got it
For some reason you have the data, filter, auto filter turned on.
In the summary sheet you had used the auto filter to select all non blankls records.
Low and behold you hid from row 18 to 100
I am not sure if you intended to use the Autofilter or not.
However 2 ways out of that.
If you still want autofilter to stay on, then click on the blue down arrow in C17 and select All from the list. You should now see all your rows come back.
If you want to turn off Autofilter completely,click on menu choice Data, Filter, Autofilter to turn it off.
I think you should be alright now
For some reason you have the data, filter, auto filter turned on.
In the summary sheet you had used the auto filter to select all non blankls records.
Low and behold you hid from row 18 to 100
I am not sure if you intended to use the Autofilter or not.
However 2 ways out of that.
If you still want autofilter to stay on, then click on the blue down arrow in C17 and select All from the list. You should now see all your rows come back.
If you want to turn off Autofilter completely,click on menu choice Data, Filter, Autofilter to turn it off.
I think you should be alright now
Joined
·
2,278 Posts
Incidentally I see that you have Tools Options View Zero Values turned on.
You do not need that turned on if you are going to use the ISBLANK formula.
The Zero Value option is a global option for all the spreadsheets and has nothing to do with the formulas.
You do not need that turned on if you are going to use the ISBLANK formula.
The Zero Value option is a global option for all the spreadsheets and has nothing to do with the formulas.
Joined
·
2,278 Posts
Just don't tell them your secret Gevans, just keep on coming back for more punishment
Seriously if you are sorted on this thread, can you mark it as Solved
Seriously if you are sorted on this thread, can you mark it as Solved
1 - 14 of 14 Posts
- Status
Join the discussion
