ESdat Forums
»
Forums
»
ESdat
»
Historical data import #SPILL! error
 Rank: Newbie
Groups: Registered
Posts: 2
Thanks: 3 times
|
Hi,
I’m having this problem when trying to import historical data into ESdat using the Historical Chem button. When the Blank Import Template pops up, the ‘SampleCode’ formula shows this error: #SPILL!, which I presume has something to do with my Excel version not being compatible with ESdat. Could anyone confirm whether I am correct? Thanks ! PD: Excel version 2001
|
|
|
|
|
|
Rank: Administration
Groups: Registered, Administrators Posts: 498  Location: Byron Bay Was thanked: 19 time(s) in 19 post(s)
|
Hi Mariana, A couple of other clients have reported this. It first occurred about a month ago in Office 365 when the released an update. We've seen updates to Office 365 cause problems before, although this is the first in over a year. To date they've always been resolved by further Office 365 updates. The only immediate resolution is one of the below:
- Roll back the recent Office Update or roll back all updates on your PC (which includes Office) by using a recent Windows Restore Point dated from before the issue arose. Some IT groups seem able/willing to do this option, and some don't.
- Ignore the forumula that ESdat places in the SampleCode column (showing for you as SPILL) and enter your own values for the SampleCode (they just need to be unique, which the formulae generally achieves). Recheck regularly to see if any subsequent Office updates are available and fix the issue.
Regards,
Warwick
|
 1 user thanked Warwick Wood for this useful post.
|
|
|
|
Rank: Newbie
Groups: Registered
Posts: 5
Was thanked: 1 time(s) in 1 post(s)
|
I've seen the same error a few time. This post is more for additional information than trying to solve the problem. It looks like a new feature has been added (dynamic arrays / spilled arrays) which changed certain formulas as a side effect. In particular, apparently before A:A would collapse to the value in column A current row but now it doesn't and gives the error[1]. One option is to change eg A:A to @A:A and get the old behaviour[2]. I just tried that with the formula in the Historical Chem import and seems to work. I changed from Code:=C:C & D:D & "_" & IF(E:E="","",TEXT(E:E,"dd mmm yy")) & "_" & I:I & "-" & J:J
to Code:=@C:C & @D:D & "_" & IF(@E:E="","",TEXT(@E:E,"dd mmm yy")) & "_" & @I:I & "-" & @J:J
and it looks to work as expected. Some Microsoft articles with further info: [1] #SPILL! error - Extends beyond the worksheet's edge
[2] Implicit intersection operator: @
|
1 user thanked JordonD for this useful post.
|
|
|
|
Rank: Administration
Groups: Registered, Administrators Posts: 498 Location: Byron Bay Was thanked: 19 time(s) in 19 post(s)
|
Thanks Jordan, Interesting links, thankyou. We're loath to just add the @ as a resolution as that has problems in that it isn't backwards compatible. My understanding is that this was a side effect of another change and they didn't intend to break compatibility with previous versions of Excel. However, if the issue continues to persist for too long we may need to find another approach to that formulae. Warwick Edited by user Friday, February 21, 2020 9:38:10 AM(UTC)
| Reason: Not specified
|
1 user thanked Warwick Wood for this useful post.
|
|
|
|
Rank: Administration
Groups: Registered, Administrators Posts: 498 Location: Byron Bay Was thanked: 19 time(s) in 19 post(s)
|
Hi, We've not yet seen a patch for this in the Office 365, so have developed a version of ESdat that produces the formulae in the Dynamic Array format for users who are affected. It can be downloaded from: https://ESdat.net/downlo...for_Jan2020Office365.msi The formulae in the above version won't work with versions of Excel/Office pre the Jan 2020 version of Office 365, including if the Excel file is saved and sent to someone with an earlier version of Excel. We'll keep monitoring the situation to see if a solution is available that will work with both pre and post Jan 2020 versions of Office 365. Warwick
|
|
|
|
|
|
ESdat Forums
»
Forums
»
ESdat
»
Historical data import #SPILL! error
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.