-----Original Message-----
Sandy.
sorry for late response..been out.
'the receiving' book is my way of saying:
the book that contains the links, e.g. the summary book,
not the book containing the data that is being linked.
clear so far?
the toggle is just a cell,
which either contains the value 0 or 1.
and thus acts like a toggle (on/off yes/no)
situation old:
formulas pointing to databook.
situation intermediate:
formulas pointing to names.
names pointing to databook
situation final:
formulas pointing to names
depending on content of cell (e.g. C2)
the names are either pointing to 0 or to databook.
e.g.
extData1 =if(sheet1!$c$2=0,0,'[x:\folder\file.xls]sheet1'! A1)
for the users NOT interested or allowed the link,
you set the cell C2 to 0.
if you want to work with the links you set the cell's
value to 1 (or at least <> 0)
pls let me know if it's clear.
else feel free to email a workbook (ZIPPED!)
to address below and I'll work on it tomorrow.
keepITcool
< email : keepitcool chello nl (with @ and .) >
< homepage:
http://members.chello.nl/keepitcool >
Sandy said:
Thanks for your reply!
Well, I followed the logic until the part about putting
something in the "receiving book." What do you mean
by "receiving book"?
Also, how do you set up a "toggle" that tells Excel that 0
means don't use links and 1 means to use links?
Thanks again!
Sandy
-----Original Message-----
Sandy,
maybe a lot of work once a book is ready,
but worth a try
to manage external links...
activate cel A1, then for each sheet in
external file define a NAME like
extData1 ='[x:\folder\file.xls]sheet1'!A1
extData2 ='[x:\folder\file.xls]sheet2'!A1
NOTE the names are RELATIVE references, that's
why you MUST enter them from cell A1)
NOW in formulas point to the name instead of
directly to the external file (search & replace).
now you can manage the names (probably no more then 5)
instead of the filelinks.
you might do this with code but folliwing is easier:
in the 'receiving' book use sheet1!c2 as a "toggle"
(0=dont use links,1=use links)
then redefine the above names to following
(again remember to activate cell a1 first)
extData1 =if(sheet1!$c$2=0,0,'[x:\folder\file.xls]
sheet1'!
A1)
etc..
TIP:
you can alse have a look at following addins:
FindLink (Bill Manville)
FlexFind (Jan Karel Pieterse)
both from
http://www.bmsltd.co.uk/MVP/MVPPage.asp
keepITcool
< email : keepitcool chello nl (with @ and .) >
< homepage:
http://members.chello.nl/keepitcool >
Need code for the following situation:
Person A sends spreadsheet to Person B. Person B
does
NOT
need to use links. Person B makes changes and sends
spreadsheet back to Person A. Person A then needs to have
the original links restored.
I know that Excel 2002 has an option for breaking links.
Don't know if this is what I need. At any rate, I don't
myself have 2002 and therefore can't create a macro to
find out the language.
Any suggestions will be greatly appreciated!
Sandy
.
.
