Moving Target?

[mniedert]

I have a spreadsheet I use to track inventory. Setup like this:

Data Center Spare Pool Analysis.xls
	A	B	C	D	E	F	G
1		DesiredPool	Min	Current			CurrentSpare
2		forPlannedMargin	Threshold	Variance		8/23/2006	8/30/2006
3
4	6'Hose	6	44	-2		44	42
5	10'Hose	20	20	81		108	101
6	15'Hose	15	15	-6		12	9
7	25'Hose	3	2	-1		1	1
8	40'Hose			0
9	60'Hose	3	2	-1		1	1
Sheet3

I want the Current Variance column to calculate only on the data in the column which has the heading "Current Spare". That way I could move "Current Spare" from column to column and the Variance would change.

 

[Oaktree]

D4=INDEX(F4:IV4,MATCH("Current Spare",F$1:IV$1,0))-C4
copied down

Or, if your "Current Spare" is always going to be the rightmost value in the row, try:

D4=LOOKUP(9.99999999999999E307,F4:IV4)-C4
copied down

The advantage of the latter is that you don't have to remember to put "Current Spare" in a cell for the update to happen.

 

[mniedert]

Thanks! The first formula works great.