I have got a budgeting spreadsheets in Excel 2003 as follows:
1) "Data Entry {1-10}" worksheets
2) On each data entry spreadsheets, there is an "aggregate" function, which
is invokable with a click on a Button. The code behind each button on each
of these data entry worksheet is as follows:
2.1) It Set ThisWorkbook.objCurWorkSheet =
Worksheets("currentWorksheetName")
2.2) It invokes ThisWorkbook.processItem
2.2.1) processItem is a workbook level global function that aggregate data
from the current data entry worksheet into a "Overall Operating budget"
worksheet
3) I have 3 seperate "Team operating budget" worksheet that function as
follows (they are intended to be a "view" into the "Overall Operating
budget" worksheet:
3.1) The values are obtained as hyperlinks into the "Overall Operating
Budget" worksheet
For instance cell G19 in "Team1 Operating budget" is set to "='Overall
Operating Budget'!G62"
3.2) "Team{1-3} Operating Budget" worksheets are locked

This has been working fine, but recently the linking seem to stop working
and values no longer seem to be linking to values in "Overall Operating
Budget" worksheet. I have the following observations:
1) Even if I unlock the "Team{1-3}Operating Budget" worksheets, the links
are not updating the value
2) The values are NOT updated when I close and re-open the worksheets
3) The only way the values seems to be updating is if I go into the cells
with linked values in "Team{1-3} Operatin Budget", press F2 to modify cell
(but actually makes no changes), then press ENTER, and the value is
refreshed instantly
4) If I make further changes in "Data Entry {1-10}", pressed the Aggregate
button to update values in the "Overall Operating Budget" worksheet which
change the value that the updated cell of 3 above is linked into, then the
value in the cell updated at 3 above does not change until I repeat step 3!!

How is this possible and how could I fix this?

Hi,

Thanks for posting in the group!

I have reviewed your thread. Currently I am finding somebody who could help
you on it. We will post back in the newsgroup as soon as possible.

If there is anything unclear, please feel free to post in the group and we
will follow up there.

Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Patrick,

To isolate the problem, I think we may try first to copy the excel file
onto another machine with the same office version to see the problem
persists, so that we will know if the problem is in the excel file or the
offce product environment.
If the problem can be reproduce on another machine, the problem may be
caused by the file itself, I think we may try to recreate an excel and
then copy the Macro to the the new one.
Otherside, we may try to repair installed the office product on the problem
machine to see if that works for you.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

This is Very *weird* !

1) Problem is reproducible on other PCs also running Office 2003
Professional on Win XP Pro
2) I have to press F9 to get the links to reappear (I am 101% sure I didn't
previously have to do this)
3.1) If I am on Worksheet "Team1 Operating Budget", Cell E21, with a value
of "='Overall Operating Budget'!E10" with a value of 123
3.2) If I select Cell E21 on "Team1 Operating Budget" worksheet and press
Crtl+C to copy
3.2) If then move to cell E22 on "Team1 Operating Budget" worksheet and
press Crtl+V to paste, then cell E22 get a forumla of "='Overall Operating
Budget'!E11" *and* a value of 123 (when on E11, of "Overall Operating
Budget" the value is actually 222). I had to press F2 and Enter to get the
value updating!
3.3) If at 3.2 I instead select past special to paste formula, also the
paste doesn't get me a value of 222 !


""Peter Huang"" wrote in message
...
Hi Patrick,

To isolate the problem, I think we may try first to copy the excel file
onto another machine with the same office version to see the problem
persists, so that we will know if the problem is in the excel file or the
offce product environment.
If the problem can be reproduce on another machine, the problem may be
caused by the file itself, I think we may try to recreate an excel and
then copy the Macro to the the new one.
Otherside, we may try to repair installed the office product on the
problem
machine to see if that works for you.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no
rights.

Hi Patrick,

Follow your steps, I can not reproduce the problem on my machine(excel
2003, windows xp pro).

So can you create new worksheet to do the test to see if the problem
persists?

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Would rather not have to go down that route! Since the XLS file contain
many different worksheets each with 2 VBA buttons in them that sets a global
variable denoting the worksheet being worked on before invoking a global
function defined at the Workbook level.


""Peter Huang"" wrote in message
...
Hi Patrick,

Follow your steps, I can not reproduce the problem on my machine(excel
2003, windows xp pro).

So can you create new worksheet to do the test to see if the problem
persists?

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no
rights.

Hi Patrick,

Yes, it would be a big project to recreate such a workbook. So I suggest
you create a new workbook and just in one cell input the formular which
linked to another sheet and then copy the formula into the next cell to see
if the link is updated so that we can judge if the excel application has
any problem.

If you can send me a reproduce sample I would be appreciated so that I can
forward it to our dev team for further troubleshooting. Or I think you may
try to contact MS PSS.
http://support.microsoft.com

If you still have any concern, please feel free to let me know.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.