Update formula based on single cell value

[aboud60]

Is there a way to create a sum formula that changes its range based on the value in a separate, static cell? For example, I want to be able to change the month in a static cell and have that month update a sum formula to create a new YTD value. If it is set to August (or 8), update a sum formula to sum 8 months in a row. Using Excel 2007 under Windows XP.

This is my first posting. Thanks.

 

[Oaktree]

Welcome to the board!

Sure, you could use e.g. =SUM(B2:INDEX(B2:M2,A4)) where A4 stores your 8 value that you'd change.

 

[T. Valko]

Is there a way to create a sum formula that changes its range based on the value in a separate, static cell? For example, I want to be able to change the month in a static cell and have that month update a sum formula to create a new YTD value. If it is set to August (or 8), update a sum formula to sum 8 months in a row. Using Excel 2007 under Windows XP.

This is my first posting. Thanks.

Maybe something like this...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	8	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	_	31	10	4	91	70	58	66	36	2	32	27	93
Sheet1

A1 = the month number

=SUM(B2:INDEX(B2:M2,A1))

Note that if A1 is an empty cell the formula will calculate the entire range.

 

[aboud60]

Thanks guys I tried it and it works great! Now my next problem is to sum the remaining months into a different cell. Is there any easy way to accomplish that?

Thanks again!

 

[T. Valko]

Thanks guys I tried it and it works great! Now my next problem is to sum the remaining months into a different cell. Is there any easy way to accomplish that?

Thanks again!

Let's assume you have that formula in cell A10.

=SUM(B2:M2)-A10

 

[aboud60]

Ha-ha. That was so obvious, I missed it. Thanks again! You have been a big help.

 

[T. Valko]

Ha-ha. That was so obvious, I missed it.
Thanks again! You have been a big help.

You're welcome. Thanks for the feedback!