Hi, hoping you clever people can help please.
I have a long formula in a report that looks at values in a particular column that is specific to months. On the 1stof the following month I have to roll the formula forward so that each column value increases by 1 i.e. AD becomes AE, AE becomes AF etc. I do this with a find an replace at the moment starting with the furthest column. This works ok but bit time consuming and not easy to ask someone else to update. Can you suggest a quicker, more fool proof way of doing this? Only columns that don't change are C and D. Would be happy to use VBA. Here is an example of the formula. Thank you for any suggestions
=IFERROR(IF(SUM($C7:$D7)>=SUM(AD7:AL7),"9.00+",IF(SUM($C7:$D7)>SUM(AD7:AK7),8+((SUM($C7:$D7)-SUM(AD7:AK7))/AL7),IF(SUM($C7:$D7)>SUM(AD7:AJ7),7+((SUM($C7:$D7)-SUM(AD7:AJ7))/AK7),IF(SUM($C7:$D7)>SUM(AD7:AI7),6+((SUM($C7:$D7)-SUM(AD7:AI7))/AJ7),IF(SUM($C7:$D7)>SUM(AD7:AH7),5+((SUM($C7:$D7)-SUM(AD7:AH7))/AI7),IF(SUM($C7:$D7)>SUM(AD7:AG7),4+((SUM($C7:$D7)-SUM(AD7:AG7))/AH7),IF(SUM($C7:$D7)>SUM(AD7:AF7),3+((SUM($C7:$D7)-SUM(AD7:AF7))/AG7),IF(SUM($C7:$D7)>SUM(AD7:AE7),2+((SUM($C7:$D7)-SUM(AD7:AE7))/AF7),IF(SUM($C7:$D7)>AD7,1+((SUM($C7:$D7)-AD7)/AE7),SUM($C7:$D7)/AD7))))))))),"")
I have a long formula in a report that looks at values in a particular column that is specific to months. On the 1stof the following month I have to roll the formula forward so that each column value increases by 1 i.e. AD becomes AE, AE becomes AF etc. I do this with a find an replace at the moment starting with the furthest column. This works ok but bit time consuming and not easy to ask someone else to update. Can you suggest a quicker, more fool proof way of doing this? Only columns that don't change are C and D. Would be happy to use VBA. Here is an example of the formula. Thank you for any suggestions
=IFERROR(IF(SUM($C7:$D7)>=SUM(AD7:AL7),"9.00+",IF(SUM($C7:$D7)>SUM(AD7:AK7),8+((SUM($C7:$D7)-SUM(AD7:AK7))/AL7),IF(SUM($C7:$D7)>SUM(AD7:AJ7),7+((SUM($C7:$D7)-SUM(AD7:AJ7))/AK7),IF(SUM($C7:$D7)>SUM(AD7:AI7),6+((SUM($C7:$D7)-SUM(AD7:AI7))/AJ7),IF(SUM($C7:$D7)>SUM(AD7:AH7),5+((SUM($C7:$D7)-SUM(AD7:AH7))/AI7),IF(SUM($C7:$D7)>SUM(AD7:AG7),4+((SUM($C7:$D7)-SUM(AD7:AG7))/AH7),IF(SUM($C7:$D7)>SUM(AD7:AF7),3+((SUM($C7:$D7)-SUM(AD7:AF7))/AG7),IF(SUM($C7:$D7)>SUM(AD7:AE7),2+((SUM($C7:$D7)-SUM(AD7:AE7))/AF7),IF(SUM($C7:$D7)>AD7,1+((SUM($C7:$D7)-AD7)/AE7),SUM($C7:$D7)/AD7))))))))),"")