Forum Discussion
Blank cells
I have 2 spreadsheets, one as a database, the other as a display. Column H on spreadsheet 2 is a "closed date" so when this row is complete a date is put in column H. So to get up to date information on my display spreadsheet I have used a filter formula to copy all of the rows where column H is empty. My formula is a bit like this:
=FILTER(Sheet1!$A$J, Sheet1$H$H="").
This works fine to copy the 30-40 live rows I need into the display but it also populates all of the blank cells below that in rows A - J with 0s. This doesn't really affect the functionality of the spreadsheet but I think it does slow it down quite a lot. When I input something to the display, it takes a good 5 - 6 seconds to change based on the formulas including this page. Is there a way I can modify my filter formula so that it does not try and copy anything below the last entry of the database spreadsheet?
just add another condition like:
=FILTER(Sheet1!$A:$J, (Sheet1$H:$H="")*(Sheet1$A:$A<>""))but better yet would be to 'Format as a Table' the data on Sheet1 and then use table reference. So select a cell in the data table on Sheet1 and then under Home select 'Format as a Table' and pick any style you like. Then make sure the autoselection picks your whole data range and if the table has headers. Then after it is made into a 'table' under the 'Table Design' tab you can NAME your table something meaningful like 'Data'. Then your formula would be:
=FILTER(Data, Data[close date]<>"")
and this will be applied only to the rows of the table instead of the whole sheet. If you don't want to return all the columns then something like:
=FILTER(Data[[date]:[open date]], Data[close date]<>"")
assuming you want the columns from "date" through "open date" (this of course will depend on your actual table header names.
2 Replies
just add another condition like:
=FILTER(Sheet1!$A:$J, (Sheet1$H:$H="")*(Sheet1$A:$A<>""))but better yet would be to 'Format as a Table' the data on Sheet1 and then use table reference. So select a cell in the data table on Sheet1 and then under Home select 'Format as a Table' and pick any style you like. Then make sure the autoselection picks your whole data range and if the table has headers. Then after it is made into a 'table' under the 'Table Design' tab you can NAME your table something meaningful like 'Data'. Then your formula would be:
=FILTER(Data, Data[close date]<>"")
and this will be applied only to the rows of the table instead of the whole sheet. If you don't want to return all the columns then something like:
=FILTER(Data[[date]:[open date]], Data[close date]<>"")
assuming you want the columns from "date" through "open date" (this of course will depend on your actual table header names.
- Awesome hopefully that speeds up my formulas a little. The database sheet is already formatted as a table, but it's on a shared drive and not all users have full access to it, hence why I'm just trying to copy the live information out of it as simply as I can. Thanks for your help 👍
