#Spill! Error - can't figure out why

[Paul at GTS]

Hi.
I have the formula below in range H6:H581

=IF([@[2023 Total number of visits]]=0,"Not seen in 23",INDEX($AB$1:$IB$1,1,IF(MAX(IF(ISBLANK(AllMembers53[@[SUN01]:[SUN53]]),0,COLUMN(AllMembers53[@[SUN01]:[SUN53]])))-31<0,0,MAX(IF(ISBLANK(AllMembers53[@[SUN01]:[SUN53]]),0,COLUMN(AllMembers53[@[SUN01]:[SUN53]])))-31)))

All cells except for 2 work as expected. H479 + H481 give me a #Spill! error - claiming that you can not use the formula in a table - which I accept but why do all the other cells in the range work correctly ? If I convert the table to a range I still get the same issues, but this time the cause is given as spill cells being blocked - again I have checked this and its not the case.

If i copy the working cell H478 to H479 I stll get the issue.

What am I missing ?

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Are all the columns blank when you get the error?

 

[Paul at GTS]

Hi Fluff
Have updated profile, thanks for the advice.
Windows 10 Version 10.0.19045 Build 19045
Office 365 Microsoft® Excel® for Microsoft 365 MSO (Version 2306 Build 16.0.16529.20100) 64-bit

No, not all the columns are blank to the right of the formula, but then neither are they in row 478 which works fine.

 

[Fluff]

Thanks for updating your profile.

not all the columns are blank

In that case can you post some data that shows the problem?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.