Copy rows from multiple sheets to final "summary" sheet and not include empty "blank" rows

[mikeb1028]

OS:  Windows 11

Excel: 365

I have several sheets that perform price calculations.  The user will enter a quantity, type, options, and each row is formulated.  One sheet has 9 rows, another sheet has more or less.

On the final sheet, I want to create an invoice listing their items, one per line, but not include blank rows from the other sheets.  I will also need to copy all the details to the invoice sheet (quantity, type options, cost, etc). 

Sheet 1 uses columns A-N, rows 3-12.  The price/cost is in column L and M (cost ea and cost total based on quantity).  If the cost is "0", assume the row is blank and do not include it on the invoice page.  Same for Sheet 2.  I could simply refer to the individual cells in each sheet but I don't want to have 8 blank rows before showing the next sheet items.  Here is 1 sheet.

If I can manipulate this data - combine information in one cell, on the final invoice page, that would be a plus.  But I have to get the rows to the invoice first.

Thanks,

Mike

PS:  I'm not sure if VBA would be best, but this should also be compatible with Excel for mobile.  I've found some form controls are not compatible with mobile versions.

[#2]

it is really hard without a sample sheet. To maximize compatibility i would suggest sticking with sheet functions. Since you are on 365 you can get pretty fancy with the LET() and FILTER() functions. For example you could use FILTER for each sheet to only include rows that aren't empty and then 'build' them back together using counts and choose functions. I imagine something like (untested and just off the top of my head):
LET(a, FILTER(sheet1!a1:n20, sheet1!l1:l20>0,""),
b, FILTER(sheet2!a1:n20, sheet2!l1:l20>0,""),
c, FILTER(sheet3!a1:n20, sheet3!l1:l20>0,""),
s, SEQUENCE(rows(a)+rows(b)+rows(c)),
IFS(s<=rows(a),index(a,s,),s<=rows(a)+rows(b), index(b,s-rows(a),),true,index(c,s-rows(a)-rows(b),)))

[#3]

Thank you for the response. Today at work I searched and found some VBA code to show/hide rows based on the value in a cell. In my case, if the value carried over is "0" - unused value from the other sheet, it would hide the row. I found Worksheet_SelectionChange which does the trick in realtime but to change the status (hide/unhide), I have to click somewhere on the sheet where this code is located and acting on. It is working, though.

We are now looking at replacing the tablets with Windows-based tablets so VBA and other developer functions can be used.

I will look at your code some more.

Thank you again!
Mike