Linked Workbooks

[Guest]

I have a spread sheet that is liked to another work book. The formula in one cell reads as follows

=OFFSET('[New Sales Commission Table (version 1).xls]Sheet1'!$B$2,MATCH(B152,'[New Sales Commission Table (version 1).xls]Sheet1'!$A$3:$A$1874,0),MATCH(G152,'[New Sales Commission Table (version 1).xls]Sheet1'!$C$2:$AC$2,0))*F15

When I open the dependent worksheet (the one that contains this formula), it asks if I want to update. It doen't matter if I sellect "Update" or "Don't Update" the worksheet appears to try and update and I get an error for the cell of "#VALUE".

The only way I don't get the error message is to have the supporting workbook (New Sales Commission Table (version 1).xls) open prior to opening the workbook which contains the formula above.

I want to be able to update the dependent workbook without opening the supporting workbook. Is there a way to do this? It seems to work for other workbooks I use but not the one that contains this formula

Thanks for you help.

 

[Dave Peterson]

I think you got hit by two things.

First, I think =offset() requires that the workbook be open.

Second, xl2002 (or xl2003??) likes to recalculate any workbooks that were
created in previous versions. (Are you using xl2002+?)


Jim Rech posted a registry tweak:
http://groups.google.com/[email protected]

I have a spread sheet that is liked to another work book. The formula in one cell reads as follows:

=OFFSET('[New Sales Commission Table (version 1).xls]Sheet1'!$B$2,MATCH(B152,'[New Sales Commission Table (version 1).xls]Sheet1'!$A$3:$A$1874,0),MATCH(G152,'[New Sales Commission Table (version 1).xls]Sheet1'!$C$2:$AC$2,0))*F152

When I open the dependent worksheet (the one that contains this formula), it asks if I want to update. It doen't matter if I sellect "Update" or "Don't Update" the worksheet appears to try and update and I get an error for the cell of "#VALUE".

The only way I don't get the error message is to have the supporting workbook (New Sales Commission Table (version 1).xls) open prior to opening the workbook which contains the formula above.

I want to be able to update the dependent workbook without opening the supporting workbook. Is there a way to do this? It seems to work for other workbooks I use but not the one that contains this formula.

Thanks for you help.

 

[Frank Kabel]

Hi Dave
Excel 2003 behaves the same way opening files created in older versions

--
Regards
Frank Kabel
Frankfurt, Germany

I think you got hit by two things.

First, I think =offset() requires that the workbook be open.

Second, xl2002 (or xl2003??) likes to recalculate any workbooks that were
created in previous versions. (Are you using xl2002+?)


Jim Rech posted a registry tweak:
http://groups.google.com/groups?threadm=uzkujhMHEHA.3284@TK2MSFTNGP11
..phx.gbl

I have a spread sheet that is liked to another work book. The formula in one cell reads as follows:

=OFFSET('[New Sales Commission Table (version

1).xls]Sheet1'!$B$2,MATCH(B152,'[New Sales Commission Table (version
1).xls]Sheet1'!$A$3:$A$1874,0),MATCH(G152,'[New Sales Commission Table
(version 1).xls]Sheet1'!$C$2:$AC$2,0))*F152formula), it asks if I want to update. It doen't matter if I sellect
"Update" or "Don't Update" the worksheet appears to try and update and
I get an error for the cell of "#VALUE".supporting workbook (New Sales Commission Table (version 1).xls) open
prior to opening the workbook which contains the formula above.the supporting workbook. Is there a way to do this? It seems to work
for other workbooks I use but not the one that contains this formula.

 

[Guest]

Dave:

Thnaks for your thoughts. Both workbooks were created in the same version of Excel 2002. I think your comments on the Offset function are correct.

 

[Dave Peterson]

I was hoping that =offset() to a closed workbook would only give an error if you
told excel to try to update the links.

I just tried in in xl2002 and I guessed wrong. xl wants to calculate that cell
with =offset() in it.

There are some formulas that won't update if you tell xl not to refresh the
link. =offset() ain't one of them.

Maybe you could use =index(). It seems to work ok if the workbook is closed and
won't update if you tell excel not to.