changing formula 1

[jmnekrkt]

I have the following formula:
=SUM(C9,F9,I9,L9,O9,R9,U9,X9,AA9,AD9,AG9,AJ9)
which I need to change based on current month ie after month 1, the formula would be =SUM(F9,I9,L9,O9,R9,U9,X9,AA9,AD9,AG9,AJ9).

The problem is that this formula repeats for every row of as many as 150 rows. I've been trying to use the Name function to create a formula that I can change once and then use the name as the formula for each row. All I've been able to do is total ALL rows in to one.

Any suggestions?

Thank you

 

[DaleWatson123321]

I've tested the following and it works...

1) First choose a cell where you'll be entering a value for the month. This cell can be on any sheet. Assign a range name of "mth" to this cell.

2) Use the following formula, and copy it to the rows required.

=CHOOSE(mth,SUM(C9,F9,I9,L9,O9,R9,U9,X9,AA9,AD9,AG9,AJ9),SUM(F9,I9,L9,O9,R9,U9,X9,AA9,AD9,AG9,AJ9),SUM(I9,L9,O9,R9,U9,X9,AA9,AD9,AG9,AJ9),SUM(L9,O9,R9,U9,X9,AA9,AD9,AG9,AJ9),SUM(O9,R9,U9,X9,AA9,AD9,AG9,AJ9),SUM(R9,U9,X9,AA9,AD9,AG9,AJ9),SUM(U9,X9,AA9,AD9,AG9,AJ9),SUM(X9,AA9,AD9,AG9,AJ9),SUM(AA9,AD9,AG9,AJ9),SUM(AD9,AG9,AJ9),SUM(AG9,AJ9),SUM(AJ9))

3) The formula is set up so that if you enter the number "12" into the "mth" cell, it will sum the full 12 months. When you enter "11", it will sum 11 months, etc.

Method of Assigning a Range Name:
a) Highlight a cell or range-of-cells
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[RobBroekhuis]

Nice solution, Dale. But to go back to the original approach of doing it programmatically: why not just change the formula at the first row and copy it down to all the others? That would work just fine. No need to mess with range names.
Rob

 

[jmnekrkt]

Thanks Dale, works great. I had resorted to filling the unwanted fields w/zero...this will be much faster.
Rob, Copying down each month would not work because there are other formula's in between on the way down and they are not all in the same location.

Thanks both for the prompt response.

Debby