SPILLs of external data - word of warning


Just as a heads-up to fellow Excel programmers.  SPILLs (via FILTER) are obviously a great technique to import data from external databases.  The syntax to reference the imported results in eg A100:Y103 is the very handy "A100#".  All of this works very well in the presence of these databases. 


When we periodically supply outside parties with our production Excels, we do not export these databases (because in my experience we end up spending countless hours supporting the recipient in staging our production environment on their system).  To facilitate the data exchange, we only supply standalone versions of those Excels, ie only workbooks with broken links.


Now, here the nub:  When you break the link to the data source, Excel will freeze the imported results in A100:Y103 *)  That is exactly what we want.  So far, so good. 


BUT ...


The reference "A100#" now produces a #REF! error because that range is no longer a SPILL (but an ordinary range of numbers in the sheet).  To my money, this is an oversight by Microsoft.  Break Links would need to replace all references to "A100#" with the fixed span "A100:Y103".


To work around this issue, I put aside an oversized fixed span eg A100:BA103.  To the right of that span in BB100:BB103, I put some text (instructions on what to do with #SPILL errors).  I then put the FILTER call, as before, in A100.  But instead of referencing A100#, I now reference the fixed span A100:BA103, providing code (IFs) to deal with blank cells.


So long as the imported data can fit into the fixed span, all is well.  If FILTER spills over the end of the fixed span, the text to the right of it forces a #SPILL error, therefore preventing accidents.


*) If you are lucky, that is!  Data/Edit Links/Break Links is arguably the most unreliable feature in Excel and we use a VBA macro to largely replace this functionality, but that's a matter for a separate post.

0 Replies