Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing the target worksheet
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing the target worksheet
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()). zhj23 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing the target worksheet
Thanks for your suggestions.
vlookup() is something new to me. I shall explore it further. zhj23 "Dave Peterson" wrote: 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()). zhj23 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing the target worksheet
Debra Dalgleish has lots of notes on =vlookup() and =index(match()) he
http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) zhj23 wrote: Thanks for your suggestions. vlookup() is something new to me. I shall explore it further. zhj23 "Dave Peterson" wrote: 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()). zhj23 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
Excel VBA Target Worksheet change | Excel Discussion (Misc queries) | |||
Worksheet locked for editing | New Users to Excel | |||
Using VBA to create a new worksheet, and then target new worksheet | Excel Discussion (Misc queries) | |||
What do I need so I can open an Excel worksheet for editing? | Excel Worksheet Functions |