Comparing multiple sheets / Updating like cells on each sheet

Hello everybody

I'm working on developing a "Stock Balancing" report within a workbook containing 4 sheets - each sheet represents a separate branch.

Sheet 1 will show items needed in location 1 and "overstock" for locations 2, 3 and 4

Sheet 2 will show items needed in location 2 and "over stock for locatins 1, 3 and 4

Sheet 3 shows items needed in location 3 and overstock for 1,2 and 4

Sheet 4 shows items needed in location 4 and overstock in 1, 2 and 3

I can write the formulas needed to figure qtys needed for one branch, however, I think I will need a macro to update the qtys on the subsequent sheets.

For example, if location 1 and 2 need an item and depletes overstock in location 3, location 4 (sheet 4) should not look to fill material from location 3.

If anyone is able to help start me on the right track, I would very much appreciate it. Please let me know if any further clarification is needed.

Here's a scaled down example..