Should be simple?

[Wotsie]

Hi,

I have multiple (hundreds) of archived spreadsheets consisting of the same mix of tabs and content format (only the data is different). I want to add an extra tab (lets call it 'Tracker') to pull bits of data from the various tabs into one easy to copy n paste column. Simple.
What I then need to do is copy the same Tracker into all of the other spreadsheets....But, obviously when I paste this to another sheet, it tries to reference the original spreadsheet rather than just pull from the same tabs in the open sheet. It feels like this should be simple but I'm failing miserably...can you help?
I realise there may be some kind of Xlookup thing I can do to pull from all the sheets independently, but that's not what's required in this case.

Thanks in advance.

 

[Logit]

.
Search for COPY VALUES NOT FORMULAS.

 

[Wotsie]

.
Search for COPY VALUES NOT FORMULAS.

But I need the formulas to copy, just don't need them to add the reference to the original sheet?

 

[mikerickson]

As I understand it you have a Tracker sheet in one workbook. There are formulas on that sheet. You want to copy Tracker to other workbooks and have those formulas point to the new workbook, rather than to the previous one.

One way to do that would be to use INDIRECT throughout all of your formulas.

Instead of =Sheet1!A1 + Sheet2!B2
use =INDIRECT("Sheet1!A1")+INDIRECT("Sheet2!B2")

If you are copying with VBA rather than manualy, you can copy the formulas from the original tracker to the new tracker with code like. (this approach does not require the use of INDIRECT)

Dim originalTracker as Worksheet, newSheet as Worksheet
'...
' code to make new sheet

With originalTracker
    newSheet.Range(.UsedRange.Address).FormulaR1C1 = .usedRange.FormulaR1C1
End With

 

[WaterGypsy]

Could you use Data/Edit Links then change the Source to the current file ?