Reference Cell not Updating

[Mlogs]

Hello,

I am trying to setup a spread sheet where 1 cell (A1) equalsa cell in another spreadsheet. In theformula bar it looks like =[Book1]Sheet1!$B$1. This works perfectly well, however when the value in Book1 at B1 needsto be updated we copy the column, insert the copied column over top of the existingand hide the previous column. This meansthat the current value now in Book1 is at C1 and the old value is atB1. If I have both spreadsheets openthere are no issues. However we havemultiple spreadsheets looking at Book1 and if any of them are closed, thereferenced cell does not change, they will still be looking at B1 and not C1.


Is there a simpleish way to achieve this functionality, to have cells referenced in a closed workbookupdate when the referenced workbook’s column shifts.


Thank you

 

[Legacy 433589]

Okay, there's a lot going on here and I think you may need to be clearer in your problem description.

To create a static reference to one cell in another cell that will always look at, for example, cell B5 no matter what inserts or deletes happen, I believe you want the INDIRECT function.

However, I don't believe that you will ever get any references to closed workbooks to work. In order to get the data from the closed workbook Excel would need to open it.

Can you be more specific about how you are updating the referenced workbook?

 

[Mlogs]

A simple example is:
In Book1 cell A1 = book3 cell B1
In Book2 cell A1 = book3 cell B1
In Book3 cell B1 = 10, all other cells are empty.

Book1 is open
Book2 is closed

I open Book3 copy Column B and insert the copied cells before Column B. Now B1 and C1 both equal 10. I change the value of C1 from 10 to 20.

Book1 cell A1 updates to 20 because the formula follows the cell to its new position of C1.

When you open Book2 cell A1 still equals 10 because the formula still references cell B1 in Book3.

Is there a way to make the formula is Book2 update when the column was added in Book3 even when Book2 is closed?

 

[Mlogs]

A simple example is:
In Book1 cell A1 = book3 cell B1
In Book2 cell A1 = book3 cell B1
In Book3 cell B1 = 10, all other cells are empty.

Book1 is open
Book2 is closed

I open Book3 copy Column B and insert the copied cells before Column B. Now B1 and C1 both equal 10. I change the value of C1 from 10 to 20.

Book1 cell A1 updates to 20 because the formula follows the cell to its new position of C1.

When you open Book2 cell A1 still equals 10 because the formula still references cell B1 in Book3.

Is there a way to make the formula is Book2 update when the column was added in Book3 even when Book2 is closed?

Just checking to see if anyone has an answer for this yet. Is this only available via VBA, or is there a way to setup a function to update a cells location in another workbook when the formula's workbook is closed?

Thank you

 

[Legacy 433589]

I don't think you can do this the way you are going about it - relying on each workbook to update itself in response to changes in the other workbooks using functions in the cells themselves.

I'm also looking into making references to closed workbooks. I don't think you'll be able to find a way to update a closed workbook, but you could go at this two different ways which would effectively get you to the same place.
1 - Write a macro in Book3 that when you trigger it makes the necessary changes to Book1 and Book2 whether or not they are open (if they are not open, you'll have to write code to open, edit and then close them again).
2 - Write autorun macros in Book1 and Book2 that examine Book3 (open or closed) and change themselves in response to any changes to Book3.

Both approaches have their merits and challenges. The first one will be easier code to write, but then requires positive action on the 'user' side. The second one would seem to work in the background requiring no user intervention, but coding for potentially unpredictable changes to Book3.

Using Named Ranges in Book3 and referring to the Named Ranges in Book1 and Book2 would also almost get you there - but again, I think would require all books to be open for them all to get on the 'same page'.

 

[Mlogs]

Hi Fishrokk,

What i ultimately went with was was your last suggestion. I named every cell containing the current values we need to reference in book 3. In book 1 and 2, when making cell A1 equal cell B1 in book 3 it really isn’t looking at cell B1, it’s looking at the named cell which currently resides at B1. When we copy column B and insert the copied column on top of column B, cell B1 is now not the named cell, but actually B1. The named cell now resides in C1. And when we change the value in this cell both Book 1 and Book 2 update, no matter if they are closed or open.

There is only 1 bit of erroneous behavior in this process, but so far it has not impacted us negatively. If either Book1 or Book2 was open when we update the named cell, it will automatically update to that value. However if we don’t save book3 and close it, any open workbooks won’t revert back to the last saved value until they are closed and re-opened.

I have not figured out how to force a workbook to update all references upon closing, but like I said, this has not caused any issues for us yet. If anyone has any suggestions on this I would like to hear them.

Thank you.

 

[kvsrinivasamurthy]

Since only columns are inserted, it is sufficient to name the columns instead of each cell. Refer to range name and row

Workbook Book2
Rage name val
Row number 3
=INDEX(Book2.xlsx!val,3)

or

=INDEX(Book2.xlsx!val,Row())

 

[Legacy 433589]

I have not figured out how to force a workbook to update all references upon closing, but like I said, this has not caused any issues for us yet. If anyone has any suggestions on this I would like to hear them.

Since the references are contained in Book1 and Book2, there's nothing you can do in Book3 to "push" the updates from Book3 to Book1 and Book2 when you close it.

What you might be able to do is, if you're familiar with the Personal file, is write a macro that forces all open workbooks to update their references anytime a workbook is closed. The drawback is it will run every time you close a workbook, but the code shouldn't be too complicated.

But it seems like you're maybe not working the VB side of things - which is not a disparaging comment.

Good additional suggestion, kvsrinivasamurthy! I'll try to incorporate that strategy into my thinking going forward.

 

[Legacy 433589]

Forget what I said about the personal file. That would be needlessly complicated.

The macro could be put into Book3, making what I said about "nothing you can to in Book3" a bonehead statement that is completely wrong. But it will require a macro.

The code for the macro I described is probably going to be as simple as just activating every open workbook in turn prior to closing the workbook in question. So, when you click to close Book3, Excel will activate Book1 (triggering it to update its references), then activate Book2 (updating its references), then return to Book3 and complete the close operation.