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?
