Update Links

[Allison]

I have a master file that contains over 50 worksheets.
These are all links from other files. The worksheets in
my master are almost all formula driven from their
respective file which may contain 60 worksheets as well.
All of these worksheets are identifical but contain
different project info. I get the error "formula too
long" when I change the linked source (because the file
names change depending on the month). I am also running
into a problem that if the file name does change and I
update links (sometimes it allows me), I am noticing that
the formulas in the linked sheets are growing bigger and
bigger with the name of the newly named file along with
the names of all of the prior file. Not sure if this
makes sense but I do not want to spend my time copying and
pasting values/formats and column widths for each of the
individual files because the links don't work. Can
anybody suggest an easier way to do this. On top of that
the file is over 6mg which nobody in my office wants to
receive when I send out the summaries.

Any help would be greatly appreciated.

Allison

 

[Tom Ogilvy]

The link formulas shouldn't be cumulative, so however you are updating them,
it doesn't appear to be the correct method.

You probably need to copy the data from you sheets to new sheets in a new
workbook. Do pastespecial values and repeat with formats. This removes the
links and should make your workbook smaller. Only copy the rectangular
range that contains data to avoid other forms of bloat.