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

New Contributor

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 and drag it to the right it updated the cell reference incorrectly  ( it  jumps from K39 to M 39  and from C6 to F6 and I want it as a example below (from K39 to K39 and c 6 to E6 and   ( in alphabetical order  /+1 increment)

Is there a shortcut do it or a formula? 

I have also tried to select the cells individually  with a "Command"button ( I am using iMac)  and drag it but it does not  work either

 

You help would be much appreciated it!

 

Thank you

 

 
 

 

 
   
NameDaysValue $DaysValue $
John SmithPlan!K39*Plan!$K$22$C$6*D6Plan!L39*Plan!$L$22$C$6*E6
Mark GreenPlan!K40*Plan!$K$22$C$7*D7Plan!L40*Plan!$L$22$C$7*E7
   
  
3 Replies
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
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?

@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.