Help Excel links suddenly broken- could an update ?

[edt123]

Any help would great. I have put a lot of time into an excel app and it is suddenly inexplicably broken.
The simple version of the problem is this: I have sheet1 named "R" which receives the data forn BA including form etc and sheet 2 which links from it.
like this =IF('R'!B5=0,"",'R'!B5) it used to be a simpler = 'R'!B5 but that kept putting 0 in empty cells.
Now I go to sheet two and I see 0 in the cells with = 'R'!B5 format and I see blank in the ones with =IF('R'!B5=0,"",'R'!B5). When I switch to R sheet, I see that the data is all there.

Bizarrely, I jsut ran it and things seemed to work OK but: If I now highlight the cell with a runner name in it ,hit f2 and then enter, I get a 0 in the cell so the problem is still there. The prices have continued to change in R as I would expect, but not in sheet 2
The column causing most problems is the forecast because of linked formulas that need real numbers and just now I went to R sheet copied the entire column, went to sheet2 and pasted a link. Voila , it works for now. but when the race changes it will break again. Doing that with prices appears to have worked, owever, the prices dont refresh. It seems to be a byval pointer not a byref . Why would that happen?

I have experimeted with data formats to no avail. In any eevnt i have no control over R sheet, it is whatever it is. But I wondered if the data had changed in any small way?
I suspected an old rogue process being kept alive by windows or maybe by BA and my links pointing to this in some way, but a reboot has had no positive effect.
I wanted to be betting today
Thanks in advance of anyone has a suggestion or question, please dont be afraid to ask silly questions, amtotally stuck

[Captain Sensible]

Do you have any VBA running? Is the sheet on automatic calculation too?

[edt123]

Automatic is on. It is currently set at 100 iteratations.
Theres a lot of vba code though it does not do anything with these cells. I scrape the RP and fill in asperate sheet with form then do some analysis and place a few key indicators onto the working sheet.
This form data was being linked through but i changed that to just placing the data. It has made no difference.

Fundamentally the form sheet calls a function form the change event and this function mathces a horse in the working sheet to a row of form then does the calcualtions and places indicators for that horse into the working sheet.
All of this was workinh till day before yesterday.
I get the distinct impression that this is a data type issue, thats why I was aking if the BA has been aletered at all. Suddenly 0s are appearing in the sheet filled by BA.

Thanks for you interest

[edt123]

Well I spent a week rebuilding the entire thing on a fresh workbook and made soe improvements. Tested it thoroughly with the last race I had in the store ad it worked a treat.
Today I connectted to BA and got two races. I didnt notice a problm with the first byt the scond one broke everything again.

BA puts data in sheet1
sheet two has a link to sheet one
now it doent updata and hen I f2 and enter it it changes to 0
if I copy the data form sheet1 and paste a link anywhere in sheet 2 , instead of a live link, I get 0
If I do that to any other sheet, I get aperfectly working link.

A. it soent seem to be data types
B , theres no problem till BA is running.
B BA is not directly connected to the sheet that breaks it jsut has live links to cells or ranges in the sheet updated by BA

Gruss guys, how about some interest???????????????????
I have invested a lot of tuime in this at elast hello would be nice

[Captain Sensible]

Hard to say from the info you've stuck up , are you sure it's not the VBA setting it to manua calculation and not turning things back on? Do things work when you press F9? Is your worksheet on a networked drive or local to the PC? Also what version of ecel are you using as the later ones do have lots of control for data/macros you may need to enable.

[edt123]

Ok I have resolved this.
I had some support form Microsoft necaus Im a partner, though I didnt want to use it up.
They say mExcel retains a table of links in memeory and if when it recalculates internaly one of the links in unavailable i can cause havoc.
DDE can cause this to happen depeneding on the implementation.

I then did my own testing and I think my own solution is non necessarily comnected to the Microsft explanation.
I tested links element by element and I discovered that although they looked perfect, the "=" in a link had been damaged and replaced by another character. When I hae time \ill test it with ascii and see if i can generate a number to see what went on.
In any cas I wrote a short function that runs at the end of ever refresh and replaces every = in very formula with = . It runs in milliseconds and fixes the problem.

Ed

[Captain Sensible]

Yep excel and scraping does seem to bring it's own additional problems , I noticed one of my sheets that scrapes was getting bigger and bigger and slower , eventually found it was storing names on each refresh, probably cos of my poor coding . I run a routine after each market to clear names now and all works fine again


Sub DeleteDeadNames()
Dim nm As Name
For Each nm In ThisWorkbook.Names
nm.Delete
Next nm
End Sub