This is a question about Excel, not VBA. I have a simple formula in my top row that averages the ten cells below it. I want to copy the row below this one and insert a copy as a new row. (My range is most recent first and older ones fall out of the most recent ten eventually.)
When I copy and paste the row, the formula in the row above adjusts itself to ignore the row I inserted (it averages ten cells below the new row.) Why does the formula not "stick" to the 2nd through 11th rows? How do I make it do that?
For example, if my spreadsheet looked something like this (I do not have it with me):
A1 Average(A2:A11)
A2 5
A3 8
...
A11 3
A12 (unused)
and I copy row A2 and insert it there, the formula in A1 becomes: Average(A3:A12). I want it to stay with A2:A11. I tried putting in dollar signs (A$2:A$11) and the insert still changed the formula. Please help.