How to make excel add or remove rows to fit data and prevent spill error?

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.