Shifting Formula by Multiple Cells

[JordanH14]

I'm creating a spreadsheet that has multiple tables with data from different locations and then creating a grand total by summing the sub-totals from each of the tables. How would I efficiently copy and paste the formulas horizontally by 4 cells (or by any set number horizontally) as when I either highlight and the auto fill the grand total section or copy and paste the formula, it simple moves the selected cells for the formula down by one. I've attached a photo, the yellow bars all have subtotals for their own sections in their individual tables, with the grand total in the bottom right of the photo. Each grand total will add every sub total in a column, so I when I copy to formula to the next grand total cell I want it to shift the selected cells horizontally by 4 cells, how would I do this?

[#2]

@JordanH14 

One possibility would be to do it manually by copying the cell / area with the formula with (Ctrl + C). Move the mouse cursor to the cell / area you have selected and press (Ctrl + V). The formula is now copied into the cell.

You can also use the simple mouse method by marking one cell / area and then moving the mouse pointer over the small square at the lower right edge and dragging it to the cell into which the formula is to be transferred. Unfortunately, this only works if the cells are adjacent to one another.

There is also a little trick to change the formula or the reference point. You need the dollar sign ($) for this. If you place the sign ($) in the formula to make the reference absolute, it can no longer be automatically adjusted by Excel. Example, if formula is: = A1 + A2, change it to = $ A $ 1 + $ A $ 2.

Finally, you can also use VBA, here the simple variant.

Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)

    Rows (Target.Row) .Copy

    Rows (Target.Row + 1) .Insert Shift: = xlDown

End Sub

That would be like "paste copied cells".

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.