Forum Discussion
copy a consistent formula
How would I copy a consistent formula to a cell 4 columns over.
Ie copy sum( a1+b1) to 4 columns over to read sum(a2+b2)
3 Replies
Let's say you enter the first formula in D2. Enter:
=SUM(INDEX($A$1:$B$1000, QUOTIENT(COLUMN(D2)-COLUMN($D$2), 4)+1, 0))
Copy the cell with the formula, then paste it 4 cells to the right, 8 cells to the right, etc.
Thank you for illustrating my point, Hans, that the original request isn't readily accomplished in Excel. I'll give you credit for a creative solution, but I'd still want to know the context of the request, so as to consider other possibly more straight forward solutions.
How would I copy a consistent formula to a cell 4 columns over.
Ie copy sum( a1+b1) to 4 columns over to read sum(a2+b2)
Generally speaking, you wouldn't. There are ways to have that happen, but not by copying and pasting; the combinations you describe can't be handled readily by Absolute and Relative references. One way to do this would be to use INDIRECT to construct the formulas, using letters and numbers from elsewhere on the sheet (e.g., the top row).
In other words, what you're requesting -- or appear to be requesting --isn't readily accommodated by Excel. But the fact that you make the request raises (in my mind at least) a question back to you: what are you trying to accomplish that prompts you to ask this? It's easy enough--would have taken less time than it took to post the question--to just write the second formula in that cell four columns over to the right. Could you describe the context, the bigger picture, for us? There may be another solution
