External link issue

[mwifladt]

Hi all,

I set up a worksheet that had links to two separate spreadsheets. The original was a test for "January" and now that i got it fully functional i want to create copies of it for the rest of the months. Normally i would use the "Replace" option to just change out the "Jan" in the spreadsheet name to "Feb" so that it would pull from each correct month. Unfortunately Excel is not allowing me to do that and is attempting to change the data in the source workbook. Does anyone know of a way to make this work without me having to manually enter the other 11 months because the first one took about 2 hours.

Thanks in advance!

[Richard Buttrey]

ARE we talking spreadsheets or WORKBOOKS. Often people refer to spreadsheets when they mean a workbook file.
It sounds like you just need to use the Date..Edit Links..Change Source functionality.

[mwifladt]

I'm sorry i should have said workbooks. I have external links to two separate workbooks in my new workbook.

[mwifladt]

How would changing the source help me? I still want it linked to the same workbook, but just a different worksheet within that workbook.

[Richard Buttrey]

Hi,

I'm confused when you say 'it'. Can you describe this specifically, e.g

Workbook A Sheet1 links to Workbook B Sheet2
Workbook A Sheet2 Link to Workbook B Sheet3
...or whatever

and I want to copy Workbook A Sheet1 to a new Workbook A Sheet 3 which is linked to Workbook B Sheet ...whatever

..but I guess you get the idea..

[mwifladt]

Ok so i have Workbook A & B that are source workbooks. I have links from those two workbooks in Workbook C. The way that the two source workbooks are set up is that they each have monthly worksheets like "Jan 16" etc. The dependent workbook (Workbook C) has similar monthly tabs set up so that it can be more of a summary of the crucial data from both source workbooks. I set up the first tab (Jan 16) in the dependent workbook and manually set up all of the links that i wanted in place. The issue i am having is that i made 12 copies of that "Jan 16" tab with the hopes that i would be able to just use the "Replace" function in order to swap out the the month names in the link. So in the cells it looks like this "='S:\Garth\[Liftings.xlsx]Jan 16'!$I$22" I want to be able to just replace the "Jan 16" portion of the link so that it picks up the right worksheets from the source but it is changing the formula in the dependent workbook and source workbook.