I am trying to create a production order-bill of material master sheet. The issue I am having is when I use =filter to get my bom data I can only do one line item without a #ref error. I am hoping to find a way to =filter then add rows(push down the data) so data does not get overwritten.

This is working when I am only filtering for one line item

The issue comes when I drag the formula down to get the BOM info for ROW 2. Error when dragging the formula through the sheet

I have highlighted the line item and filter results I am looking for. If anyone has a solution to add X amount of rows based on the filter results that would be great. I am currently at a loss. I have tried for weeks.

CodePudding user response：

As formulas can't insert rows,

1. you might insert a large number rows below the row having the #REF! error, i.e. 100, to make room for the filter formula results to expand,
2. once the results are displayed, delete the blanks rows.

You might automate this this using the macro recorder but you will have to tweak the resulting code.

Also you might create a script from scratch, but if you will take this path, consider to do the full job using only Google Apps Script, i.e. you might use Array.prototype.filter instead the FILTER function.