Mar 12 2021 10:12 AM - edited Mar 12 2021 10:16 AM
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?
Mar 12 2021 12:40 PM
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.
Mar 12 2021 12:46 PM
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.
Mar 12 2021 12:54 PM
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.