Links and Filesizes

[scouse]

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
Worksheet.xls

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!!!

scouse

 

[Dave Peterson]

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,
that is.)

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
Worksheet.xls

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!!!

scouse