Forum Discussion
How to make excel add or remove rows to fit data and prevent spill error?
Hello!
My question is:
When using the =FILTER formula to copy cell data from one sheet to another, how can I make excel add or remove rows as needed to prevent a spill error?
I am using the =FILTER formula to copy cell data from one sheet to another if a checkbox is checked. My formula uses the cells containing the checkbox (column A) as the reference for columns B, C, D, etc. to the right. I did this by selecting the cell that has the formula on the destination sheet, highlighting this cell and those to the right I wanted to transfer, and then control + r. My formula is:
=FILTER('Source Sheet'!B5:B13,'Source Sheet'!$A5:$A13=TRUE)
(If there is a way to copy an entire row from the source sheet instead of having to control+r to apply the original formula across select cells please also let me know, thank you!). However, I want the sheet to be able to automatically add rows to fit the data if the selected number of rows is greater than what I have left available on the sheet. Currently, if I leave 3 rows for data to fill and I select three checkboxes, this will fill the space with no problem. However, if I select 4+, I get a spill error (as there are not enough empty rows to put the data). Is there any easy way to have excel add rows as needed?
Similarly, is there a way for excel to remove rows as needed?
I am going to use this sheet to detail all medications being taken by family members (with medication name, concentration, dose, time to take each in their own column as column B, column C, column D, column E). The names will be listed in column A with ~3 rows below each name which will fill when the checkbox on the source sheet is clicked. I want to ideally fit this on one page when printing without major "fit to page" problems that come up during printing.
The layout of the sheet will ideally look like this:
The goal for the 'destination sheet' is to pull only the selected data I want to use from my master list on the 'source' sheet.' I want to avoid having a big empty space under each family member where they don't take many medications, and to also not have to manually add rows for family members that take a greater number of medications.
Please let me know and thank you!
1 Reply
You first question answer is, you can filter many rows and columns using FILTER() function like-
=FILTER('Source Sheet'!B5:D500,'Source Sheet'!$A5:$A500=TRUE)I assume you need one single cell dynamic formula which will spill all outputs at a time, means you do not need manually adjust formula when you add rows in source sheet. To avoid #Spill error, you can use TAKE() function. Suppose you want to keep 3 records, then limit filter function output inside TAKE() function like-
=TAKE(FILTER('Source Sheet'!B5:D500,'Source Sheet'!$A5:$A500=TRUE),3)You may also need VSTACK() to stack all results into one formula. Please share a sample file, attach to your post or share via OneDrive, Google-Dive or Dropbox service.
