Forum Discussion

Copper Contributor

May 26, 2022

Excel - how to drag formulas to the right ( cell reference issue)

Good morning, Can you please help with this issue? I need to copy formulas placed in column B and C to the right. Both formulas are linked to another works-sheet. When I select both formulas ...

excel

Excel on Mac

Formulas and Functions

mtarler

Silver Contributor

May 26, 2022

So here is a trick you can use. When you copy or fill a formula it will change the relative references BUT when you cut and paste a formula it will keep the references the same. So:
1. COPY B&C to something to the right like F&G
2. CUT F&G and PASTE to G&H
3. COPY G&H and PASTE to D&E
4. DELETE G&H

KamilaG
Copper Contributor
May 26, 2022
I need to drag the two formulas place in column B and C to the right (24 columns in total) for each row and I have lots of rows
Is there any other way to do it?
- mtarler
  Silver Contributor
  May 26, 2022
  KamilaG You could also replace the cell reference with an INDEX in the formula something like:
  =$C$6*INDEX(6:6,1, COLUMN()/2-1)
  COLUMN() will return the column # of the cell it is in so /2 increments by 1 so you just need the correct offset.