Aug 25 2020 06:10 AM
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
Aug 25 2020 06:58 AM
You need to lock the formula by inserting $ sign or by pressing F4 inside the formula
=’34’!$E$47
Cheers
Aug 25 2020 06:59 AM
Aug 25 2020 07:00 AM - edited Aug 25 2020 07:03 AM
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 =