Pasting structured references results in link instead of normal formula

Question

If I copy cells from a table in workbookA then paste into another table in workbookB, any formulas with structured references will remain linked to the table in workbookA. However, I want the structured references to refer to the table in workbookB, just like they do for normal (non-table formatted) cells.

Specifically, if I have a cell in workbookA tableA with the formula "=[@[Start Date]]+4", when I copy that cell into tableB in workbookB, it changes to "='workbookA.xlsm'!Input[@[Start Date]]+4." I don't want this automatic link to be pasted, I want it to remain "=[@[Start Date]]+4"

If I copy a normal formula say "A4+2", it remains "A4+2" when I paste it, no link. So, is there anyway to disable automatic link creation when working with structured references?

mathetes · Answer

Bacon117&nbsp;&nbsp;I don't know if this is the answer, but it'd be the first thing I'd try. That would be to use Paste Special...Formulas&nbsp;You could also (though it may just be a "manual version" of the above) try to copy the formula from the formula bar rather than from the cell in which it appears.

bacon117 · Answer

I tried all the options in paste special, nothing changed the outcome. It seems to default to the "Paste Link" option and no way to paste anything but that.

Copying and pasting the text of the formula is a valid workaround, but if I'm copying and pasting a lot of cells, this isn't a viable option. Another option is to create a script, BUT I'm looking for something compatible with normal copy/paste first.

mathetes · Answer

Bacon117&nbsp;&nbsp;Without knowing the full context here, it's hard to suggest specific solutions. Only things to try, which may be utterly irrelevant.&nbsp;One other thing that occurs to me--again, without knowing the context--but rather than copy and paste lots of pieces, is to create a template based on that first file, and then use it in creating the second.&nbsp;If that's something you've considered and rejected, already, then rather than have us guess in the dark, maybe you could give a more complete sense of what you're working with, what you've tried, etc.

bacon117 · Answer

mathetes&nbsp;I REALLY appreciate your assistance with this.&nbsp;I don't know how to share documents here.&nbsp; But it's easy to recreate.&nbsp;&nbsp;Create and save two workbooks (I used workbookA and workbookB)In workbook A, create a table that looks like the below.&nbsp; Columns 1 and 2 are just the data, column 3 is the structered reference of "=[@[Column 2]]+5"Create a table in workbook B, but no data in the rows, just the headers.&nbsp;&nbsp;Copy the range A2:C5 in workbook A to cell A2 in workbook B. You will see that column 3 is linked to Workbook A instead of referencing the cells in workbookB, with no appearant way to NOT reference workbook A.&nbsp;&nbsp;The actual context of what I am doing:&nbsp; My team used to manage program managment calenders in excel by manually moving shapes (stars, text boxes, etc) around to keep track of important dates.&nbsp; I automated the managment of those shapes, so now users just have to enter dates in a table.&nbsp;&nbsp;I use a table as input for a script to use to create the specially formatted calendar.&nbsp; Frequently, we will copy and paste data from one input table to another workbook input table as we manage different parts of a project or start a new projct.&nbsp; I manage the script for other users to use and want to avoid creating an "Import" script.&nbsp; I'd like to try and stick to the copy/paste functionality most users are familiar with, if possible.&nbsp;&nbsp;&nbsp;At this point, I'm looking for a solution that only addresses the copy/paste functionality.&nbsp; I've googled and looked through all the options, but have yet to find any solution.&nbsp; I could easily create a VBA if it were just me using the document, but my boss uses it.&nbsp; Another possible solution is to remove all formulas from the source table so I'm only copying data.&nbsp; Creating a button to remove all references with vba before copying is probably my best workaround if I cannot find a solution with copy/paste.&nbsp; Not the best solution because we lose formulas.&nbsp;&nbsp;&nbsp;Hopefully, that's enough context.&nbsp; Please let me know otherwise.&nbsp;&nbsp;&nbsp;Thanks.&nbsp;&nbsp;-Russ&nbsp;

mathetes · Answer

Bacon117&nbsp;&nbsp;What you've described sounds very much like an ideal application for the template solution I alluded to in my last reply. &nbsp;Here is a link to a page with several resources that might help understanding how, instead of copying from your workbook A to workbook B, you create a basic version and save it as a template, from which you more easily create B without copying and pasting.&nbsp;I don't know how to share documents here.&nbsp;If the template&nbsp;references don't fit your need, then perhaps you can post a copy of the sheet you're working with on OneDrive or GoogleDrive and paste a link here that grants edit access to that sheet.

Forum Discussion

Pasting structured references results in link instead of normal formula

Share

Resources