Dynamic Expanding/Contrasting Groups of Rows?

I have a spreadsheet I am trying to improve which compiles a list of materials from multiple "groups", which the items for each group are in another sheet/tab. The "Total" sheet compiles all of the items in the various group sheets. I would like the groups of rows on the Total page to be dynamic, expanding or contrasting rows as needed when more or less items are added to the group sheets.

I have tried a few different strategies from Googling, but to no avail. If anybody can make that work it would be appreciated!

Hmm, that isn't combining data from multiple fields? Not sure how I'd implement it.

But yes I am on Excel 365.

How about

Formula:

=LET(v,VSTACK(Group1:Group4!A3:J100),FILTER(v,TAKE(v,,1)<>""))

Awesome thank you! Would there be any way to do this with ignoring any rows on the group sheets which are a 0 or blank quantity cell?

Hmm, when I open your example it shows correctly, but if I try to change any cells in any of the group sheets, the M1 cell changes to #NAME? and has an error box that says unsupported function.

How about

Formula:

=LET(v,VSTACK(Group1:Group4!A3:J100),FILTER(v,INDEX(v,,7)<>0))

Have you tried the formula I suggested?

Originally Posted by Fluff13

Have you tried the formula I suggested?

Yes that seems to be working. The only thing I am trying to figure out now is, I need some kind of label on the Total sheet for each group. I had done that with vertical text in a merged cell on the first column, as well as thick borders around each group's items. This solution, while it appears to work well, doesn't seem like it will work out with my group labeling method and I'm coming up blank on a way to show that. Any ideas?

Merged cells are an abomination & can cause all sorts of problems.
Also a formula cannot merge cells & spill formulae will not work in them.
However if you create a named range called SheetList with this formula

Formula:

=TOCOL(TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]",-1))

you can then use this formula

Formula:

=LET(s,DROP(SheetList,1),v,VSTACK(Group1:Group4!A3:J100),sht,TOCOL(IF(SEQUENCE(,ROWS(v)/ROWS(s)),s)),f,FILTER(HSTACK(sht,v),INDEX(v,,7)<>0),IF(f="","",f))

Although the workbook needs to be saved as an xlsm.

Originally Posted by MarvinP

Hi Stasis,

I'm added a column A to each of your Groups and changed the formula a bit. In M

Formula:

=IFERROR(VSTACK(Group1!A2:.K50,Group2!A3:.K50,Group3!A3:.L50,Group4!A3:.K50),"-")

See if this works for you now..

With this one, if I change anything on the group sheets the table on the Total page changes to just "-".

Yes I did, I went with K1000.

So far, this is working pretty well, but I can't get it to not fill in a 0 if the group sheet field is blank.

=LET(v,VSTACK(Group1:Group4!A3:K100000),FILTER(v,INDEX(v,,7)<>0))

If I try this one:

=IFERROR(VSTACK(_xlfn._TRO_TRAILING(Group1!A2:K50),_xlfn._TRO_TRAILING(Group2!A3:K50),_xlfn._TRO_TRAILING(Group3!A3:L50),_xlfn._TRO_TRAILING(Group4!A3:K50)),"-")

I get an Update Values popup browse window looking for a file for each group after I hit enter.

Originally Posted by MarvinP

Did you mean K instead of L??

Formula:

=IFERROR(VSTACK(_xlfn._TRO_TRAILING(Group1!A2:K50),_xlfn._TRO_TRAILING(Group2!A3:K50),_xlfn._TRO_TRAILING(Group3!A3:L50),_xlfn._TRO_TRAILING(Group4!A3:K50)),"-")

That was a typo, but changing it doesn't make any difference. With that formula all I get is a "-" in the field the formula is entered, everything seems correct though.