Dragging formulas

Copper Contributor

Can anyone help with this, 

 

I am trying to drag formulas horizontally from different workbook tabs. However when I drag formula it changes the cell rather than the workbook.

 

i.e. following on from my correct sum of =’34’!E47


I want the following sums to be =’35’!D47  =’36’!D47 ='37'!D47 etc.... changing the tab number but keeping the cell the same

 

What I actually get is

=’34’!E47  =’34’!F47   =’34’!G47   =’34’!H47

 

I hope I explained this to make enough sense for anyone who can help

 

thanks in advance

3 Replies

@MichelleE17 

 

You need to lock the formula by inserting $ sign or by pressing F4 inside the formula

 

=’34’!$E$47

 

Cheers

Copy a formula by dragging the fill handle
Select the cell that has the formula you want to fill into adjacent cells.
Rest your cursor in the lower-right corner so that it turns into a plus sign (+), like this:
Drag the fill handle down, up, or across the cells that you want to fill. ...
When you let go, the formula gets automatically filled to the other cells:

I'm afraid that is how dragging (or copying) formulas work in Excel; they always only update cell references, not worksheet names. I would do this:
- In cell A1 put the number 34
- In B1 =A1+1, copy cell B1 to the right as far as needed
- In cell A2 write a formula like this one:
="='"&A1&"'!E47
- Drag this to the right
- Copy the cells on row 2
- Paste special Values
- Do a Search and replace, search for = and replace by =