Forum Discussion
Dragging formulas
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
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 =- 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: You need to lock the formula by inserting $ sign or by pressing F4 inside the formula
=’34’!$E$47
Cheers
