Problem with breaking links to SPILLs

Iron Contributor

Has anyone solved the following problem in a satisfactory manner:


We all know the correct way to reference a Spill is "#".  But when such a Spill is the result of a reference to [SomeWorkbook]!SomeRange and you proceed to Break Links to SomeWorkbook, the formulas referencing the imported Spill fail because Break Links has replaced the Spill with an ordinary range of numbers and "#" is no longer an admissible syntax.


I have tried to accommodate this behaviour by spilling the imported data into (oversized) ranges which I reference using normal A1 style (no "#").  It is all very tiresome because I must teach the dependent formulas to deal with trailing blanks.  Additionally, I must trap Spills that overflow the receiving space.


This is bad enough.  Things become truly impossible when I am trying to harvest ranges by some label in the Worksheet.  This can be made to work with Spills - it is adequate to find the top left corner to reference the whole Spill - but I cannot get this to work at all with the ranges that remain after Break Links.


Any ideas?

0 Replies