How to sum a column - minus one row , to avoid circular calculations ?

[StrawberryDreams]

Basic data calculator test 8c.xlsm
	D	E	F	G	H	I	J	K	L	M
22	1	Using the Selection Entry Table, the user picks both the Season and Target Product, then enters an amount ( number of items )
23	2	The Selection Entry Table will take the item value from the Inventory Table using Index/Match which it does already and ( multiplies the value by the Amount in the Entry table ) and assigns that number to the product column which matches with the season and product dropdown.
24	3	I would like the Remaining Target Value to be the Indexed value from the Yearly goal table ( which it does based on matching product with the entry table Target Product)
25		and subtract the corresponding column total ( one row above ) so that it doesn’t include itself in a circular calculation.
26		If possible, the Remaining Target Value in yellow should be ( = 200 - 60 = 40 remaining) before entering a value in Amt. needed.
27
28	4	IF a value of "1" is entered in the Amt. then the grand total for pants will be 70 but the remaining target value should then be 200 - 70 = 30, but this might cause a circular reference ?
test sheet (3)

Basic data calculator test 8c.xlsm
	E	F	G	H	I	J	K	L	M
2	YearlyGoal			InventoryTable	Item Value
3	Product	Yearly Target Value		Outfit Name	Shirt	Pants	Hat	Dress	Gloves
4	Shirt	100		Summer	10	10	10	10	10
5	Pants	200		Fall	20	20	20	20	20
6	Hat	150		Winter	30	30	30	30	30
7	Dress	300		Spring	40	40	40	40	40
8	Gloves	100
9
10
11				Grand Total >>	0	60	10	0	60
12	SelectionEntry
13	Remaining Target Value	Amt needed	Choose Season	Target Product	Shirt	Pants	Hat	Dress	Gloves
14	200	1	Fall	Pants		20
15	150	1	Summer	Hat			10
16	200	1	Spring	Pants		40
17	100	2	Winter	Gloves					60
18	200		Summer	Pants		0
19
test sheet (3)

Cell Formulas
Range	Formula
I11	I11	=SUM(SelectionEntry[Shirt])
J11	J11	=SUM(SelectionEntry[Pants])
K11	K11	=SUM(SelectionEntry[Hat])
L11	L11	=SUM(SelectionEntry[Dress])
M11	M11	=SUM(SelectionEntry[Gloves])
I14:I19	I14	=IF([@[Target Product]] = SelectionEntry[[#Headers],[Shirt]], [@[Amt needed]]* (INDEX(InventoryTable1825[[#All],[Shirt]],MATCH([@[Choose Season]],InventoryTable1825[[#All],[Outfit Name]],0))),"")
J14:J19	J14	=IF([@[Target Product]] = SelectionEntry[[#Headers],[Pants]], [@[Amt needed]]* (INDEX(InventoryTable1825[[#All],[Pants]],MATCH([@[Choose Season]],InventoryTable1825[[#All],[Outfit Name]],0))),"")
K14:K19	K14	=IF([@[Target Product]] = SelectionEntry[[#Headers],[Hat]], [@[Amt needed]]* (INDEX(InventoryTable1825[[#All],[Hat]],MATCH([@[Choose Season]],InventoryTable1825[[#All],[Outfit Name]],0))),"")
L14:L19	L14	=IF([@[Target Product]] = SelectionEntry[[#Headers],[Dress]], [@[Amt needed]]* (INDEX(InventoryTable1825[[#All],[Dress ]],MATCH([@[Choose Season]],InventoryTable1825[[#All],[Outfit Name]],0))),"")
M14:M19	M14	=IF([@[Target Product]] = SelectionEntry[[#Headers],[Gloves]], [@[Amt needed]]* (INDEX(InventoryTable1825[[#All],[Gloves]],MATCH([@[Choose Season]],InventoryTable1825[[#All],[Outfit Name]],0))),"")
E14:E19	E14	=IFERROR(INDEX(YearlyGoal[[#All],[Yearly Target Value]],MATCH([@[Target Product]],YearlyGoal[[#All],[Product]],0)),"")

Cells with Data Validation
Cell	Allow	Criteria
G14:G20	List	=$H$4:$H$7
H14:H19	List	=$E$4:$E$8

 

[StrawberryDreams]

oops it should read 200-60 = 140, or 200-70 = 130

 

[StrawberryDreams]

Looking for a formula to provide the results in Column A. Column B allows a dropdown to select which column you want the formula to apply to in Column A.

I guess I'm kinda after a running total that Sum's an indexed column, but everything one row above from itself. I have written the values in Column A for what I want it to look like.

Basic data calculator test 8c.xlsm
	A	B	C	D	E	F
1	SUM of chossen target column of all rows above the target row In the target rows column	target to choose	A	B	C	D
2	0	A	10		10
3	15	C		15	15
4	15	B	20	10		20
5	30	A	10		10
6	20	D				30
7	40	A	15		20
Sheet15

Cells with Data Validation
Cell	Allow	Criteria
B2:B7	List	=$C$1:$F$1

 

[Cubist]

How did you get 10 for the first A when there's no row above?

 

[StrawberryDreams]

How did you get 10 for the first A when there's no row above?

I fixed that.... Human error... none of the cells have any formulas... It was just quickly written as to what I want it to do. Nice catch !!

 

[Cubist]

Thanks for confirming. Try this.

Book1
	A	B	C	D	E	F
1		target to choose	A	B	C	D
2	0	A	10		10
3	10	C		15
4	15	B	20	10		20
5	30	A	10		10
6	20	D				30
7	40	A	15		20
Sheet7

Cell Formulas
Range	Formula
A2:A7	A2	=IFERROR(SUM(DROP(CHOOSECOLS($C$2:F2,MATCH(B2,$C$1:$F$1,0)),-1)),0)

 

[StrawberryDreams]

Thanks for confirming. Try this.

Book1
	A	B	C	D	E	F
1		target to choose	A	B	C	D
2	0	A	10		10
3	10	C		15
4	15	B	20	10		20
5	30	A	10		10
6	20	D				30
7	40	A	15		20
Sheet7

Cell Formulas
Range	Formula
A2:A7	A2	=IFERROR(SUM(DROP(CHOOSECOLS($C$2:F2,MATCH(B2,$C$1:$F$1,0)),-1)),0)

This will be great thanks Cubist. Gonna plug it in and start to modify as needed. Woohoo a new excel function I've never used yet ( choose column)