3-D Reference Help

[ramaxac]

Apologies if this has already been asked. I have one "Master" page in excel with a running inventory. Subsequent pages in that workbook are fulfilled orders, and I've set it up so that each page's inventory is the same - in other words, cell B15 in Master is the starting count of Type B Brochures, cell C15 in Master is the current count after orders are fulfilled. C15 in each order sheet is the number of Type B Brochures fulfilled. How do I deduct the value in all order sheets from the Master and can I have it auto-update when I add a new order sheet?

 

[Dave Patton]

Consider the approach that follows
- insert a blank sheet before the first relevant transaction sheet; name it Start
- insert a blank sheet after the last relevant transaction sheet; name it End
- ensure only relevant sheets are in this range

Review the structure and try the formula.
N.B. You can copy this example and paste it into a clean sheet; see the icon below the F(x).

The formula requires data sheets similar to what you described.

3d 2021.xlsm
	A	B	C	D	E	F
14				Additions	Sales	Balance
15	Type B Brochures	1000		605	905	700
16
Summary

Cell Formulas
Range	Formula
D15:E15	D15	=SUM(Start:End!D15)
F15	F15	=B15+D15-E15

 

[ramaxac]

Thank you!

 

[Dave Patton]

Thanks for the feedback.