My proposition is to keep the quantities only in one place (your inventory sheet called Main).
And change it only there. In your Circuit X sheets, which as I understand show "available for this unit elements" show numbers from inventory sheet.
simple array formula (committed with Ctrl+Shift+Enter) to achieve this would be (in C2 of each of CircuitX sheet):
but if you try to use it with real file, it will probably slow down the excel noticeably. As you have probably large but limited number of different elements in your inventory quicker version would be:
assuming you have not more than 1000 elements (rows 2:1001 (or less) in Main sheet populated).
first formula is left in circuit 1 C2, the rest of cells in the attachment is the second formula (again: note that these are array formulas).
PS. to make it standard formula and your excel recalculating quicker you could have helper column D in Main sheet. Formula in D2:
and copy down.
and then formula (as I said, non-array = standard, Enter committed) in any of Circuit sheets could read (in C2 and copy down):
Bookmarks