Forum Discussion

Ceddster's avatar
Ceddster
Occasional Reader
Jul 15, 2026

Grouping data sourced from another sheet in that workbook.

I am trying to make a monthly inventory count workbook through excel nut am having trouble consolidating the counts. I have a made a simple version of what I need but am getting stuck on the final sheet trying to group the data together.

In my workbook I have a sheet with all my input data which I have attached below. I have fed this through PowerQuery so that the information from this Sheet can go into all other sheets on that workbook, and I only need to update one Sheet if new stock comes in, or leaves.

 

 

 

 

From there I have the "Bar" Sheets where the counts get entered. As I will be dealing with a lot of stock, felt better to make a "Loose" & "Cartons" column and then just sure make an extra column where the calculations can be made giving a full total amount.

 

 

 

Last Sheet is where I would like to consolidate all the totals from the "Bar" sheets into one area. which I have used the formula ="SheetName"[@Cell] as I do not know any other ways of bringing across this data. 

 

And here lies the problem. As on the "Full Stock List" sheet i have cells that are not actual values, the rows are not grouped, and if I try to sort by Item Name alphabetically, only the original 5 columns will sort, leaving the new columns untouched.

 

If anyone can help explain how I can get around this, that would be most helpful, as I need to make this Workbook much much bigger.

 

Formulas and Functions

1 Reply

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Jul 15, 2026

    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.