Remove workbook reference but keep worksheet references

[Gene.laz]

Hi,

I have 15 workbooks in which the same set of sheets appear (one named
for every month: Jan, Feb, Mar, Apr, etc. plus a summary sheet.)

On the summary sheet is a column of formulas that reference ALL sheets
in the WB. For example:

Jan!A1+Feb!A1+Mar!A1...

When I try to copy this column from one WB's summary sheet to other
workbooks, Excel inserts a workbook reference at the paste location:

['Workbook 1.xls']Jan!A1+['Workbook 1.xls']Feb!A1+['Workbook
1.xls']Mar!A1......

I really don't want that workbook name there, since all WBs contain
different data! I just want to keep the formula relative to the WB in
which I'm currently working.

Is there a way to tell Excel, in the formula itself, to disregard the
link with the copy source WB??

Thanks in advance,
~G

 

[Thomas [PBD]]

Looks like what is happening is that the Workbook is assuming that you would
like to keep the references from the Original Workbooks.
If you Change the Source, it should solve the problem.

Edit>Links> Change Source> Navigate to [Workbook2.xls]
The [Workbook2.xls] should not appear in the new formula. It will happen
over all of the links that have [Workbook1.xls].

 

[Pete_UK]

Another way is to highlight the cells with those formulae in the
destination sheet and do Find/Replace (CTRL-H):

Find what: ['Workbook 1.xls']
Replace with: leave blank
Click Replace All

Hope this helps.

Pete