Summary of the problem: I would like excel NOT to link to the spreadsheets links I'm copy and pasting until I have the entire spreadsheet setup.
Overview: I have a worksheet that acts to aggregate a bunch of data from a number of additional workbooks, and when I'm adding a new column of data I generally copy a previously linked column and then do a find and replace to get the different dataset from the same columns (i.e. in the first column I'm pulling in named cell Data1 from each linked workbook and I want to pull in the named cell Data2 so I would copy/paste the Data1 column and then find and replace the new column to get named cell Data2 from each workbook)
The issue is, that I don't have just one workbook that does this, I have many and have to build new ones regularly and when I copy and paste a linked file Excel lags as it pastes in the new column and I guess starts linking to the workbooks. I would like it to not do this and allow me to get all my columns setup (it does this for each one), find and replaced, and then I can update the links. Kind of like how if you set your calculations to manual you can setup a bunch of different formulas and then hit F9 to update everything all at once. Unfortunately setting Excel to manual calculate doesn't stop it from going through the process of linking to the named cells for each workbook when a whole column is copy and pasted and there's significant lag time when I'm setting up a new workbook.
If anyone knows a good way to solve this issue, it would save me a great deal of time when I'm setting up new aggregation work books.
Better Example…Maybe Also just for clarity when I say linked cell I mean a cell that's formula is C:\Some folder\someworkbook.xlsm'NamedCellNumberOne and I'll have a Column with a bunch of these to the NamedCellNumberOne in a number of different workbooks and I want to copy and paste that and bring in NamedCellNumberTwo in another column. This is when it lags and is slowing down building these things out.
Best Answer
Depending on the volume of the data you are copying, this may not be a viable option but...
You could copy the formula text for all of the linked cell without the equals sign with minimal slowdown and, once everything is in place, use CONCATENATE or search and replace to add the equals sign to the beginning of all of those text strings then let it crank out all of those links at once.
Let's say all of the data is in a range adjacent to "NamedCellNumberOne" You could paste "C:\Some folder\someworkbook.xlsm'NamedCellNumberOne" in the first cell then drag the corner of the cursor to "fill series" as needed. Once that is done, search all instances of "C:\Some folder\" and replace them with "=C:\Some folder\" to create all the formulas at once.