Editing the target worksheet

[Guest]

My target worksheet (just one) is linked to several source worksheets (about
50 files). If I want to edit the target worksheet (say, adding additional
rows), does it mean that I have to open all my sources such that the links
can be kept?

I had tried to edit the target without opening the sources, the linked
addresses will all be jumbled up after adding the new rows.

If it is indeed required for the sources to be opened, can EXCEL open 50
files at the same time? Any memory problem here?

Thanks.

zhj23

 

[Dave Peterson]

One way to make sure the links aren't messed up is to make sure the "receiving"
workbooks are open--so that they know that something changed in the "sending"
workbook.

Another way is to use a range name in the "sending" workbook. Excel is smart
enough to know when that name has changed location.

And one more...

Maybe you don't have to rely on formulas that point to an individual cell???

If you have a key column that contains a unique key (and that key is common in
all the workbooks), you may be able to use formulas like =vlookup() or
=index(match()).

 

[Guest]

Thanks for your suggestions.

vlookup() is something new to me. I shall explore it further.


zhj23

 

[Dave Peterson]

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))