Formulas that carry from one workbook to another

%3CLINGO-SUB%20id%3D%22lingo-sub-2205781%22%20slang%3D%22en-US%22%3EFormulas%20that%20carry%20from%20one%20workbook%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2205781%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20multiple%20formulas%20that%20refer%20to%20a%20sheet%20in%20the%20current%20workbook%20entitled%20LTBHS.%26nbsp%3B%20I%20need%20to%20copy%20this%20formula%20to%20other%20workbooks%20but%20always%20want%20to%20refer%20to%20the%20sheet%20in%20the%20CURRENT%20workbook%3F%26nbsp%3B%20How%20to%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2205781%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2206137%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20that%20carry%20from%20one%20workbook%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F995742%22%20target%3D%22_blank%22%3E%40Beverly_Ironside%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20in%20the%20formula%20bar%2C%20select%20the%20formula%2C%20then%20copy%20it%20(Ctrl%2BC).%3C%2FP%3E%0A%3CP%3EIn%20the%20other%20workbook%2C%20select%20the%20target%20cell%2C%20click%20in%20the%20formula%20bar%20and%20paste%20(Ctrl%2BV).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EP.S.%20You%20don't%20need%20SUM%20in%20your%20example%20formula.%20%3DLTBHS!S6%20is%20equivalent%20to%20%3DSUM(LTBHS!S6)%20but%20shorter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2206152%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20that%20carry%20from%20one%20workbook%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20but%20since%20I%20have%20several%20of%20these%20formulas%2C%20I%20was%20hoping%20there%20would%20be%20some%20way%20to%20automate%20the%20process%20so%20I%20won't%20have%20to%20copy%20or%20re-create%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2206206%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20that%20carry%20from%20one%20workbook%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F995742%22%20target%3D%22_blank%22%3E%40Beverly_Ironside%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20copy%20a%20range%20from%20one%20workbook%20to%20a%20range%20in%20exactly%20the%20same%20position%20in%20the%20other%20workbook.%3C%2FP%3E%0A%3CP%3EThe%20formulas%20will%20look%20like%20%3DSUM(%5BFirstWorkbook.xlsx%5DLTBHS!S6)%3C%2FP%3E%0A%3CP%3EUse%20the%20Replace%20dialog%20(Ctrl%2BH)%20to%20replace%20%5BFirstWorkbook.xlsx%5D%20with%20nothing.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have multiple formulas like the following [=SUM(LTBHS!S6)] that refer to a sheet (entitled LTBHS) in the current workbook.  I need to copy these formula to other workbooks but always want to refer to the sheet in the CURRENT workbook instead of referring to the original workbook in which the formula was created?  How to do this?

3 Replies

@Beverly_Ironside 

Click in the formula bar, select the formula, then copy it (Ctrl+C).

In the other workbook, select the target cell, click in the formula bar and paste (Ctrl+V).

 

P.S. You don't need SUM in your example formula. =LTBHS!S6 is equivalent to =SUM(LTBHS!S6) but shorter.

@Hans Vogelaar 

 

Thank you but since I have several of these formulas, I was hoping there would be some way to automate the process so I won't have to copy or re-create the formula.

@Beverly_Ironside 

You could copy a range from one workbook to a range in exactly the same position in the other workbook.

The formulas will look like =SUM([FirstWorkbook.xlsx]LTBHS!S6)

Use the Replace dialog (Ctrl+H) to replace [FirstWorkbook.xlsx] with nothing.