Display Column Header When Specific Cell is Exceeded

[nathanthomson11]

I am hoping someone can help me with a formula that will essentially display in column A, which calendar week when a specific line exceeds the number in column B. For example, in row 3, the capacity is "800" - beginning in C3 and moving across to the right, at which point does it exceed "800"... in this case it's in J3 so I want it to display cw27 (Column header) in A3.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
2	Runout	Capacity	cw20	cw21	cw22	cw23	cw24	cw25	cw26	cw27	cw28	cw29
3	cw27	800	100	50	200	175	200	20	30	75	90	100
4	cw27	400	40	40	75	80	30	35	25	90	90	40
5	cw22	150	50	50	100	40	80	90	10	10	90	100
6	cw21	200	150	150	200	400	200	50	60	70	80	100
Sheet1

 

[johnnyL]

@nathanthomson11 It took me a few reads of your question to understand what your goal is. I think if I give some clarification, it might help you get more responses from the other members.

You want to sum the values in each row, starting from the 'C' column until the sum is greater than the value from the 'B" column in that row, at that point you want to take the column header from row 2 in the column that made the sum of that row greater than the value from the 'B' column in that row, then copy that column header to the "A" column for that row.

That sound correct?

 

[nathanthomson11]

Yes, that is exactly what I'm looking for!

Apologies, I was having difficulties explaining it.

 

[kweaver]

I suspect there's a dynamic array solution, but I haven't come up with it (yet). How about this alternative:

Book9
	A	B	C	D	E	F	G	H	I	J	K	L
2	Runout	Capacity	cw20	cw21	cw22	cw23	cw24	cw25	cw26	cw27	cw28	cw29
3	cw27	800	100	50	200	175	200	20	30	75	90	100
4	cw27	400	40	40	75	80	30	35	25	90	90	40
5	cw22	150	50	50	100	40	80	90	10	10	90	100
6	cw21	200	150	150	200	400	200	50	60	70	80	100
Sheet2

Cell Formulas
Range	Formula
A3:A6	A3	=INDEX($B$2:$L$2,1+MATCH(TRUE,SUBTOTAL(9,OFFSET($C3,0,0,1,COLUMN($C3:$L3)-COLUMN($C3)+1))>=B3,0))