Hi, I was reading through other posts to try to find my answer but I wasn't getting anywhere. I am creating a sheet in Excel now and, because I am not as advanced as I'd liked to be, it is taking way too long. I know this can be done faster but I don't know how. My dilemma is that I am trying to copy and paste a formula which generates a sum of a certain range of cells. When I paste it, I need to formula range to change by 4 columns.

For example, my first formula is:

=SUM('Coster TV'!J3:M4)

and when I paste it I need it to become:

=SUM('Coster TV'!N3:Q4)

Rather than getting the formula I would like, it gives me:

=SUM('Coster TV'!L3:O4)

Does this make any sense? Please let me know if you need further clarification about my problem.

Based on the examples you gave, I am going to assume that you are pasting the formula 2 columns to the right of the original. To get the results you want, you would need to paste the formula 4 columns to the right. Excel is not a mind reader. Let's say you wanted to copy/paste your original formula and end up with the N3:Q4 version directly under the original, or maybe 4 cells to the left and 1 up. How would Excel know that? It can't.

Therefore, Excel is doing the best it can by incrementing the column value by the same number of columns the pasted formula is offset from the original. I guess we could say that we're lucky that it even does that for us.

It's possible that you could write some VBA code, or maybe use an INDIRECT function, to get the formulae you want wherever you want, but a simple copy/paste is going to follow the built in rules.

Sorry.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History