Formula Question

[ARIZONA3433]

Hi,

I have several SUM formulas that reference the same consecutive rows but
different columns (Example "=SUM(L14:L100)" ).

I would like to change the cell references to all the formula at one time.

Is this possible?

Thanks

 

[Andrew Poulsom]

Select all the formulas, amend the reference in the first and press Ctrl+Enter.

 

[ARIZONA3433]

Thanks for that suggestion.

I was hoping to have an imput from something similar to this:

Period Row Range 14 To 100

Where the number are in separate cells but they would modify the formulas
when changed.

Can this be done?

Thanks

 

[Andrew Poulsom]

Say A1 contains the start row and A2 contains the end row. You can use a formula like this:

=SUM(INDEX(L1:L100,A1,1):INDEX(L1:L100,A2,1))

Make sure the cell with the formula is outside the indexed range, or you will get a circular reference. Also make sure that neither A1 nor A2 are greater than the number of indexed rows, or you will get a #REF! error.

 

[ARIZONA3433]

Thank you!

It worked perfectly.