First, since I'm not really sure what you're doing, you can ignore all this.
But I don't like using direct links to a worksheet in a different workbook. If
you open the "sending" workbook (w/o the "receiving" workbooks being open), you
can make it so the formula links don't point at the correct address anymore.
If possible, I'd try to put some kind of index key into the data. Then use
index/match or vlookup to retrieve values. (This kind of think might not be
feasible in your situation.)
And maybe you could send out just the Results workbook and have the recipients
just answer no to the update links prompt. (if you don't need to refresh them,
Another option, maybe send out the results workbooks with no links (no formulas
at all???) and then when you get them back, reapply any formulas based on the
changes in data.
And my opinion only about the links (one going back to the external worksheet
and several referring to that one--or just each cell having its own link):
I think I'd use just one cell and point at that one from other spots. That way,
if something changes, I can fix one and the others will be ok.
But this is based on being lazy and not smart enough to be able to catch all the
links later--not based on file size at all.
(Harddrive space is cheap--manual effort searching and fixing is expensive.)
> Hi all,
> i have two queries about XL.
> I am currently developing a system which is used to measure
> performance. This is a hefty sized file consisting of two workbooks
> 1. Rough Work Workbook has all the formulas needed for the system and
> the working out
> the information i need in the 2nd workbook (which we will call Results)
> is linked to Results just simply by clicking the "=" sign and clicking
> on the cell in the Rough Work workbook.
> The Rough Work workbook is not intended to be used/seen by anyone but
> me. The Results workbook is meant to be sent to top managers to
> evaluate their performance
> When i am in the cells with links in it says
> c:/foldername/foldername[workbookname.xls] - worksheet name
> unless i save the two files as a workspace file in which case it is
> [workbookname.xls] - worksheet name
> Problem - when i send it too other people the links either get broken
> (if the two files are sent separately) or change to the new folder etc
> where they are saved.
> Is there anyway i can link the two files so that for example if they
> are saved in the same folder they will link correctly?? will i have to
> save them as a workspace (.xlw) for this?
> Finally, i take 99% of the figures out of the Rough Work workbook and
> link them into results. At the moment if there are two instances of the
> same figure each of the cells are linked to the cell in Rough
> e.g. % Annual Target Acheived appears in 3 separate worksheets in
> Results.xls therefore in say A1 there is a link to RoughWork.xls, and
> in AB102 there is a link etc. Would I save on filesize if i just linked
> one instance of a figure to RoughWork.xls and then linked internally
> (e.g. in Cell AB102 have "=A1" instead of "=[roughwork.xls] -
> worksheetname cell )
> thanks in advance and apologies for the waffling!!!
> Message posted from http://www.ExcelForum.com/