Forum Discussion
Pasting structured references results in link instead of normal formula
I REALLY appreciate your assistance with this.
I don't know how to share documents here. But it's easy to recreate.
- Create and save two workbooks (I used workbookA and workbookB)
- In workbook A, create a table that looks like the below. 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.
- 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.
The actual context of what I am doing: 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. I automated the managment of those shapes, so now users just have to enter dates in a table.
I use a table as input for a script to use to create the specially formatted calendar. 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. I manage the script for other users to use and want to avoid creating an "Import" script. I'd like to try and stick to the copy/paste functionality most users are familiar with, if possible.
At this point, I'm looking for a solution that only addresses the copy/paste functionality. I've googled and looked through all the options, but have yet to find any solution. I could easily create a VBA if it were just me using the document, but my boss uses it. Another possible solution is to remove all formulas from the source table so I'm only copying data. 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. Not the best solution because we lose formulas.
Hopefully, that's enough context. Please let me know otherwise.
Thanks.
-Russ
What you've described sounds very much like an ideal application for the template solution I alluded to in my last reply. 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.
I don't know how to share documents here.
If the template 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.
- How about you create a second table that uses the data from the first and adds the formulas (i.e. make the table you are copying have only data and no formulas)
