incrementing sum formula

Posted by Scott Allbee on February 14, 2002 9:01 AM

I have a spreadsheet with many sheets. On the first sheet I have a list with values in the rows. On the 3 sheet I have some calculations that looks as follows:

=SUM(Sheet1!B300:H351)
=SUM(Sheet1!B300:H300)
=SUM(Sheet1!B300:H378)
=SUM(Sheet1!B300:H306)

There are 112 items in this list and they are resorted every week, so I can't copy the formulas down. They also are not in any order on sheet1.

Here is the problem. I want to be able to change the last value (H300) every week to the next column. So next week I want to change all the values to say like =SUM(Sheet1!B300:I300) without doing it manually. Any suggestions?

Posted by Chris D on February 14, 2002 2:56 PM

does find/replace work ?

highlight the column
replace ":H" with ":I"
replace all

any luck ?

Posted by Scott Allbee on February 15, 2002 6:34 AM

Worked like a charm, I should have thought of that before. Thanks.