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.