Is there a way to pull columns from multiple Tables/Ranges and use Filter or Sort?

[c.clavin]

Hi guys, I'm stuck on what I believe to be a complicated one. I'm trying to employ data from 4 sheets to merge into one glanceable sheet.

The sheets I'm pulling data from are;
1. Master Items (teal)
2. Current Inventory (blue)
3. Pending Sales (red)
4. Purchase Orders (yellow)

I get the error 'You can't change part of an array' any time I attempt to filter or sort any of my columns on my results/glanceable sheet. Is there anything I can be doing to change this? Or if not, is there a different approach I should take?
I'm on the verge of tears I've been at this for so long. If anyone could please help I would GREATLY appreciate anything you could do. Thanks.

Inventory 2023.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1								IN	IN	OUT	IN STOCK
2	Item Number	Category	Manufacturer	Item Description	Cs Ct	Inventory Group	Inventory Loc.	Last Inv. Date	Inv. Count	Pending Sales	TO SELL	ON ORDER	TOTAL
3	Apple	Fruit	Del Monte	Apple	20	Package Items	Warehouse	3	12/2	1	2	40	42
4	Apricot	Fruit	Del Monte	Apricot	50	Package Items	Warehouse	4	12/2	1	3	25	28
5	Potato	Vegetable	Idaho	Potato	20	Package Items	Warehouse	2	12/2	0	2	10	12
6	Acai berry	Fruit	Del Monte	Acai berry	20	Package Items	Warehouse	3	12/2	0	3	5	8
7	Peanut	Legume	Planters	Peanut	30	Package Items	Basement	1	12/2	2	-1	0	-1
8	African mango	Fruit	Del Monte	African mango	25	Non-Package Items	Warehouse	10	11/30	1	9	0	9
9	African cherry orange	Fruit	Del Monte	African cherry orange	20	Package Items	Warehouse	2	11/30	0	2	0	2
10	Alaskan bunchberry	Fruit	Del Monte	Alaskan bunchberry	15	Package Items	Warehouse	1	11/30	0	1	0	1
11	Araza	Fruit	Del Monte	Araza	20	Package Items	Basement	40	11/30	1	39	25	64
12	American red raspberry	Fruit	Del Monte	American red raspberry	20	Non-Package Items	Warehouse	11	11/30	0	11	200	211
13	Ambarella	Fruit	Del Monte	Ambarella	20	Package Items	Warehouse
14	American persimmon	Fruit	Del Monte	American persimmon	20	Package Items	Warehouse
At a Glance

Cell Formulas
Range	Formula
A3:G34	A3	=MASITEM
H3:H12	H3	=VLOOKUP(A3,INVENTORY,9,FALSE)
I3:I12	I3	=VLOOKUP(A3,INVENTORY,8,FALSE)
J3:J12	J3	=VLOOKUP(A3,PENDING,8,FALSE)
K3:K12	K3	=H3-J3
L3:L12	L3	=VLOOKUP(A3,PO,8,FALSE)
M3:M12	M3	=L3+K3
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
INVENTORY	='Inventory Sheets'!$A$2:$I$36	H3:I12
MASITEM	=Items!$A$2:$G$33	A3
MASTER	=Items!$A$2:$G$323	A3
PENDING	='Pending Sales'!$A$3:$H$57	J3:J12
PO	='Purchase Orders'!$A$3:$V$158	L3:L12

 

[Automatrix]

Make connections to each sheet/Table using Power Query. Then you can merge the queries to get the data you need.

 

[c.clavin]

I'm trying this and it's not making any difference. I should have explained my issue better.

Say the data columns are A:G, when I add any columns after G like 'Last Inv. Date', whenever I filter/sort my table, the numbers get all out of whack. It's pulling the row letter that I'm adding the 'Last Inv. Date' (on a different sheet) with the row letter of the data columns. Is there a way around this? I'm so miserable right now I think I just spent the last 8 hours on something that's not even possible

 

[Automatrix]

Can you show the current inventory table?

 

[c.clavin]

I think I have *something* working, I'm using one Sheet as a 'master' which contains all data, in which I'll hide the irrelevant data in the other, more specific sheets. I'm running into a strange issue however. When I delete or adjust any cells, they seem to be hit-or-miss as to if they will update one sheet or another. On the most recent macOS release. Can you suggest anything?

 

[c.clavin]

I think Im going to pull the plug on this, this whole thing needs to be able to keep in sync, if I change Inventory say, the Master's unchanged inventory information stays unchanged, and reverts my changes back to it as well. This is crazy