Formulas/References

[Josh]

I have a spreadsheet where I add a new column every week and therefore have
to change the formulas every week too. The new column is inserted in column
H and everthing else is shifted down. I am having a problem finding a
formula or reference that will allow me to keep the following formula
constant.

=SUM($BH$5:CL5) which is in cell F5

When I add the column it shifts to BI5:CM5. I want the $B$H5 to stay and
the CL5 to continue to shift down every time I add another column. Relative
or absolute references do not make a difference as they change when a new
column is added. Anybody got any suggestions.

 

[M Kan]

Extend your formula to extend one column after your current formula and shade
it grey to mark it. The next week, insert your column between the last
column of data and your grey-shaded column, and the formulas should
automatically adjust.

 

[Josh]

There are other formulas in the cell adjacent to where the problem formula is
located.

 

[Lars-Åke Aspelin]

I have a spreadsheet where I add a new column every week and therefore have
to change the formulas every week too. The new column is inserted in column
H and everthing else is shifted down. I am having a problem finding a
formula or reference that will allow me to keep the following formula
constant.

=SUM($BH$5:CL5) which is in cell F5

When I add the column it shifts to BI5:CM5. I want the $B$H5 to stay and
the CL5 to continue to shift down every time I add another column. Relative
or absolute references do not make a difference as they change when a new
column is added. Anybody got any suggestions.

Try this:

=SUM(INDIRECT("BH5"):CL5)

Hope this helps / Lars-Åke