Edit Formula but Keep Existing Range

[jwb21]

I currently have the following formula taking the sum of daily data and dividing by the number of productive hours for the week to get weekly utilization data:

=SUM(FN36:FT36)/AA51

When there are weeks with holidays or vacation, the formula throws an error and I'd like to clean it up and include the IFERROR statement in front and "),0)" on the end while moving the daily data to another tab in the workbook. The range highlighted in blue needs to stay as it is in every cell i.e.

=IFERROR((SUM('Consolidated View - Daily'!FN36:FT36)/AA51),0)

I'm relatively new to macros so I'm not sure if this is possible and I hope the post isn't too confusing. Thanks in advance for any assistance.

[JBeaucaire]

You need to add $ symbols into your cell references to "lock" the parts that follow.

FN36:FT36 - nothing locked
$FN36:$FT36 - columns locked, rows not locked
FN$36:FT$36 - row locked, columns not locked
$FN$36:$FT$36 - row and columns locked

[jwb21]

The real issue here isn't whether the cells are locked or not. Right now all of the daily and weekly data are on the same sheet. I am moving all of the daily information to a new sheet and adding error handling at the same time.

I would just fill the series, but Excel is not picking up on the pattern. Since I am summing daily data into weekly data, the range in column A would be something like SUM(A36:G36), column B would be SUM(H36:N36), etc.

There are several years worth of this data and it has never been cleaned up. The process would involve manually going into each column, changing the formula and filling the series. At last count there are several hundred columns and I'm hoping not to have to do it manually. I need to change "=SUM(" to "=IFERROR((SUM('Consolidated View - Daily'!" before the range and then add "),0)" after the range that is currently in each cell.

[JBeaucaire]

I use a simple trick to get my formulas to adjust properly on their own for weird range needs like this...

1) Enter the first formula for summing A36:G36.
2) Now copy that cell 8 cells to the right and paste. The formula is now adjusted properly for column B.
3) CUT/PASTE the cell from where you pasted back to the cell next to the first formula.
4) Now copy the second cell 8 cells to the right and paste / cut / paste back to the cell next to the second cell.
5) Repeat ad nauseum.