SOLVED

Spill ranges turn into arrays after closing the file

New Contributor

Hello all,

 

I have written some spill formulas in a workbook and they work very fine. I'll give an example:

 

=SORT(UNIQUE(FPL[Area]))

 

However, after closing and opening the workbook again, all my spill formulas turn into arrays and I lose the references do the data table, that turn into ranges.

 

{=SORT(UNIQUE('Focal Points List'!$D$2:$D$986))}

 

After this the spill ranges stop working correctly and I lose the readability of having the column names in the formulas.

 

Any clue on what is happening?

 

I'm using Excel 365 for Enterprises version 2202.

 

Regards,

2 Replies
best response confirmed by anmattos81 (New Contributor)
Solution

@anmattos81 You may have accidentally saved the file as an xls rather than an xlsx.

@Riny_van_EekelenThank you very much! You are correct. I was working on an old file and have not realized that. In some sense I feel a little dumb now, but at the same time happy that the solution was simple.