Incrementing cell reference within formula upon a new row insert within a table

**m_arkle** · 04-29-2014, 06:10 PM

Hi,

I am a new member to the forum and this is my first post. I've looked through the archive, however was unable to get any further with this issue.

I have a button to insert a new row within a table. As well as inserting a new row, this will also copy a calculation section in which one of the formulas for a row is shown below.

=IF($J$7="Standard rate","("&$E$5&" + "&$F$5&") x "&$N$5&") = ",IF($J$7="CO standard","(("&$E$5&" x "&$N$6&") + ("&$F$5&" x "&$N$5&")) - ("&$D$5&" x "&$N$8&"))"&" = ",IF($J$7="CO reduced","("&$E$5&" + "&$F$5&") x "&$N$7&") =")))

This formula works fine upon the one row being held within the table, however I would like the cell references $E$5, $F$5 and $D$5 to be incremented to row 6 for the new calculation section created (whilst also retaining the previous calculation) upon each insert. As the calculation button inserts a new calculation section to the sheet I would need this somehow reference the new row added (within ContTable). I have tried a counter as well as trying ways with the INDIRECT and INDEX functions however was unsuccessful

An example of how I would like it to change is shown below:
=IF($J$17="Standard rate","("&$E$6&" + "&$F$6&") x "&$N$5&") = ",IF($J$17="CO standard","(("&$E$6&" x "&$N$6&") + ("&$F$6&" x "&$N$5&")) - ("&$D$6&" x "&$N$8&"))"&" = ",IF($J$17="CO reduced","("&$E$6&" + "&$F$5&") x "&$N$7&") =")))

I have copied my Add macro and calculate macro to hopefully provide a better view of what i'm trying to do.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks

Matt

**Chris 53** · 04-30-2014, 05:57 AM

Try taking the $ out of the cell address, just infront of the row number. So $J$7="Standard rate" becomes $J7="Standard rate"

The $ tells excel not to adjust that element of the cell address if the formula is copied. The way you have them set is such that you can copy the formulas and they will still reference the original cell. changing it to $J7 means that the column is anchored but the row number isn't so copying the formula down will change the formula to the next row.

Hope this helps

Chris

**m_arkle** · 04-30-2014, 04:16 PM

Hi Chris.

Thanks for the reply. I made these absolute cell references as at the time I was trying to increment them another way by one.

You are correct with the below statement ...
changing it to $J7 means that the column is anchored but the row number isn't so copying the formula down will change the formula to the next row.
... however it is the macro in which Add_MCC which is copying and pasting a cell range B7:J16. Upon the new section being added this changes the cell number to 16 within the formula. This is due to the row 16 effectively being the last row selected to copy (last variable with Add_MCC).

Thanks

Matt