Jan 20 2023 08:38 AM - edited Jan 20 2023 08:40 AM
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?
Jan 20 2023 08:59 AM
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
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.
Jan 20 2023 09:20 AM
Jan 20 2023 09:38 AM
Without knowing the full context here, it's hard to suggest specific solutions. Only things to try, which may be utterly irrelevant.
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.
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.
Jan 20 2023 10:10 AM
I REALLY appreciate your assistance with this.
I don't know how to share documents here. But it's easy to recreate.
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
Jan 20 2023 11:14 AM - edited Jan 20 2023 11:15 AM
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.
Jan 20 2023 11:34 AM