Forum Discussion
Formulas that carry from one workbook to another
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
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.
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.
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.
