easy copy/reference pasta issue!

[excel_noobie]

I've got a couple formulas I am using in cells, but when I copy and past them in the next cells 2 columns greater because the place I copy pasted them are 2 columns further.

Isn't there a way to alleviate that headache?



=Detail!W6 (this in column E)
=+'Disbursements YTD'!AG2976 (this is in column F)

say I wanted to copy column E's formula to column H, but want X6 from the Detail tab. Using the same logic for column F, I would want AH2976 from the Disbursements YTD tab.

 

[Luke M]

Ideally, re-design your layout so that the formulas are next to each other, rather than 2 columns apart.

If it's a few cells, you could do:
copy the cell in E2 to F2 (I know this is wrong column, bear with me)
Cut cell F2, and paste to H2. This will transfer the formula, with the correct adjustment.

Other final option would be to somehow use a MATCH/INDEX combination, based on column headers, but that depends on your sheet setup.

 

[excel_noobie]

I thought there was something like "offset" we could be build to do it for us.

 

[Luke M]

I suppose...if it's always 3 columns, could do:
=OFFSET(Detail!$W$6,,int((COLUMNS($A$1:A$1)-1)/3))
which then will let you put the formula in E and H (or any cells 3 columns apart)