Dragging formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-1610509%22%20slang%3D%22en-US%22%3EDragging%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610509%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20anyone%20help%20with%20this%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20drag%20formulas%20horizontally%20from%20different%20workbook%20tabs.%20However%20when%20I%20drag%20formula%20it%20changes%20the%20cell%20rather%20than%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei.e.%20following%20on%20from%20my%20correct%20sum%20of%20%3D%E2%80%9934%E2%80%99!E47%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20want%20the%20following%20sums%20to%20be%20%3D%E2%80%9935%E2%80%99!D47%26nbsp%3B%20%3D%E2%80%9936%E2%80%99!D47%20%3D'37'!D47%20etc....%20changing%20the%20tab%20number%20but%20keeping%20the%20cell%20the%20same%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20actually%20get%20is%3C%2FP%3E%3CP%3E%3D%E2%80%9934%E2%80%99!E47%26nbsp%3B%26nbsp%3B%3D%E2%80%9934%E2%80%99!F47%26nbsp%3B%20%26nbsp%3B%3D%E2%80%9934%E2%80%99!G47%26nbsp%3B%20%26nbsp%3B%3D%E2%80%9934%E2%80%99!H47%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20I%20explained%20this%20to%20make%20enough%20sense%20for%20anyone%20who%20can%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610594%22%20slang%3D%22en-US%22%3ERe%3A%20Dragging%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770360%22%20target%3D%22_blank%22%3E%40MichelleE17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20lock%20the%20formula%20by%20inserting%20%24%20sign%20or%20by%20pressing%20F4%20inside%20the%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D%E2%80%9934%E2%80%99!%24E%2447%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECheers%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610599%22%20slang%3D%22en-US%22%3ERe%3A%20Dragging%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610599%22%20slang%3D%22en-US%22%3ECopy%20a%20formula%20by%20dragging%20the%20fill%20handle%3CBR%20%2F%3ESelect%20the%20cell%20that%20has%20the%20formula%20you%20want%20to%20fill%20into%20adjacent%20cells.%3CBR%20%2F%3ERest%20your%20cursor%20in%20the%20lower-right%20corner%20so%20that%20it%20turns%20into%20a%20plus%20sign%20(%2B)%2C%20like%20this%3A%3CBR%20%2F%3EDrag%20the%20fill%20handle%20down%2C%20up%2C%20or%20across%20the%20cells%20that%20you%20want%20to%20fill.%20...%3CBR%20%2F%3EWhen%20you%20let%20go%2C%20the%20formula%20gets%20automatically%20filled%20to%20the%20other%20cells%3A%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610603%22%20slang%3D%22en-US%22%3ERe%3A%20Dragging%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610603%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20afraid%20that%20is%20how%20dragging%20(or%20copying)%20formulas%20work%20in%20Excel%3B%20they%20always%20only%20update%20cell%20references%2C%20not%20worksheet%20names.%20I%20would%20do%20this%3A%3CBR%20%2F%3E-%20In%20cell%20A1%20put%20the%20number%2034%3CBR%20%2F%3E-%20In%20B1%20%3DA1%2B1%2C%20copy%20cell%20B1%20to%20the%20right%20as%20far%20as%20needed%3CBR%20%2F%3E-%20In%20cell%20A2%20write%20a%20formula%20like%20this%20one%3A%3CBR%20%2F%3E%3D%22%3D'%22%26amp%3BA1%26amp%3B%22'!E47%3CBR%20%2F%3E-%20Drag%20this%20to%20the%20right%3CBR%20%2F%3E-%20Copy%20the%20cells%20on%20row%202%3CBR%20%2F%3E-%20Paste%20special%20Values%3CBR%20%2F%3E-%20Do%20a%20Search%20and%20replace%2C%20search%20for%20%3D%20and%20replace%20by%20%3D%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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
Highlighted

@MichelleE17 

 

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

 

=’34’!$E$47

 

Cheers

Highlighted
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:
Highlighted

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 =