3

I have a sheet like below. I want to copy all formulas from workbook B into A but without the reference to workbook B.

when I do it now I currently get a link in all my formulas to workbook B. But I want just the formulas but linked to my current workbook (workbook a)

If I copy a basic sum formula it works but when the formula involves a table, the reference is to workbook B.

here is a sample formula,

=IF(Start!$D$8="Weekly",0.000001*SUMIFS(tbl_database[Value],tbl_database[Node],H$2,tbl_database[Type],$A14,tbl_database[Accounting],$C$1,tbl_database[Filter],"No"),0.000001*SUMIFS(tbl_database[Value],tbl_database[Node],H$2,tbl_database[Type],$A14,tbl_database[Accounting],$C$1,tbl_database[Source],"<>stack",tbl_database[Source],"<>overflow"))

workbook A Sheet a:

enter image description here

workbook B sheet B

enter image description here

if I go to the formula bar and copy the actual formula from workbook B into A, I get the expected result, but as you can see with all these formulas, it can be tedious.

15
  • 1
    I cannot reproduce. Clearly you and I are doing something differently, I just wonder what. Could you please, show one simple formula that exposes the problem? Aug 2, 2018 at 18:12
  • Have you tried Paste Special -> Formulas? Aug 2, 2018 at 18:26
  • @cybernetic.nomad , yes I have tried, it still references workbook B. Which I don't want. I want to just paste the formulas itself and have it reference the current workbook.
    – excelguy
    Aug 2, 2018 at 18:54
  • @TomBrunberg , I got some more info. If I copy a cell with a SUM , but formulas from workbook B that reference a table, I get the reference to workbook B.
    – excelguy
    Aug 2, 2018 at 18:58
  • 1
    A single Find+Replace action after pasting can resolve this issue.
    – David
    Aug 2, 2018 at 19:11

2 Answers 2

0

Tested in Excel 2010

Copying any portion of the table from A to B will reference back to A. Copying the entire table (must include the headers!) does not reference back to A - unless the formula has a reference to a different table or a different sheet. Formulas in the table that reference a range outside the table but on the same sheet will properly reference B.

A solution (albeit not a very good one) is to ensure the table does not have any formulas that reference another table or sheet before copying to B. You could do this by migrating your data in A to the same sheet.

As mentioned in the comments using Ctrl H to find all references to the sheet and replace with a nullstring is a quick way to resolve the issue after pasting.

Not mentioned in the comments is the possibility of named ranges pointing back to A, quite likely with broken links. You can try to mitigate this by using worksheet scope (opposed to workbook) but it is likely you'll still need to edit/delete them in the name manager after pasting into B.

I'm keen on VBA and believe the best solution is to write the code that generates the original report in A. This would remove the requirement of copying from A to B, simply run the program to generate a new report as needed.

0

Not very elegant but . . . . why not allow the references to copy across and then use edit/replace to remove them all?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .