MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Changing values in two places


Posted by Call me stupid but... on May 31, 2000 3:38 AM

Two workbooks with two values in different places.
If i change one i want the other to automatically update to the other regardless of the workbook being open. Possible? It would be ok if the other updates the next time it is opened.


Posted by Jill-ann on May 31, 2000 9:26 AM

It's easiest to start with both open. In the appropriate cell of the secondary workbook, simply type the equals sign ("=") and then, with that cell still active click on Window, choose the primary workbook and click on the cell that contains the data. It will automatically put in the workbook and worksheet information. You have just created a Link. The next time you open the secondary sheet it will ask you if you want to Update Links? You can either say Yes and it will update the data or you can say no and open the primary file whenever convenient. Depending on the number of links, saying "yes" to update links may take a long time, so it might be easier to say "no" and just open the file.

Does that help?

If this is indeed your first foray in to links, I recommend that, after creating the link, you click on Edit, Links, and see all the options available to you there...


Posted by Call Me Stupid but.. on May 31, 2000 11:39 PM

Changing values in two different places

These two differnt workbooks are summaries of different workbooks. onre of the values is always on the same page but the other values are one each in many other workbooks. It is not practical to have every workbook open. Actually if i can fix a formula then it will solve this problem by making it obsolete.
=SUM(INDEX($A15:$AV1775,MATCH($A$2,$A15:$A1775,0),7):INDEX($A15:$AV1775,MATCH($A$3,$A15:$A1775,0),7))
a2 and a3 are dates.
If one of the dates is not on the sheet then it gives me an error message. What i need it to do is to take the date in a2 or the next higher date and the date in a3 or the next lower date. Then all the dates can be the same. This is used for daily weekly and monthly reports and if something is not run on a certain day then an error comes out. I use this formula a lot of times to sum up the values in many rows between two dates. Hope this helps someone cause its got me stumped. I tried using a -1 and 1 instead of the 0's but that i couldn't get to work either.