Grouping data sourced from another sheet in that workbook.

IMHO it sounds like you just need a Full Stock List and add a column called Bar#.  Then you enter all the data on that sheet.  As for the 'Source Sheet' it appears that is just a reference sheet to lookup the items/catagories/carton sizes which you could then use data validation to make sure they are entered correctly on the Full Stock List.  Then you can either filter by the Bar#, or have a report page with a pivot table or Filter formula and type in or use drop down to select which bar, or have tabs for each bar.

That all said, you can pull data from multiple sheets using a formula like HSTACK('Bar':'Bar (3)'!F2:F99), that said all of the rows need to align but then you can add something like BYROW to add each row of values:

=BYROW(HSTACK('Bar':'Bar (3)'!F2:F99), SUM)

or the full format would be:

=BYROW(HSTACK('Bar':'Bar (3)'!F2:F99), LAMBDA(r, SUM(r))

But that gets you in the same position of not being able to sort or regroup the items on that Full Stock List

So in order to do that you will need to also pull the corresponding item so if "Item Id" is unique and stays with corresponding item/catagory/subcatagory that would make it easier because then you can use:

=XLOOKUP([@[Item Id]], Bar[Item Id], BYROW(HSTACK('Bar':'Bar (3)'!F2:F99), SUM))

Then when you sort or move the rows around on the Full Stock List the corresponding totals will move too.

If you want to keep each of the sheet/bar columns you can also do the same sort of XLOOKUP for each column and then the SUM of that row stays the same.