Ridiculous Error in Excel 20016 Index of of Range completely misleading

[masplin]

Can someone make this a sticky as taken me 4 hours to find this.

I have an Excel 2013 workbook and upgraded to Office 2016. all seemed to be fine. i added a few calculated column and measures etc. I then went into the powerpivot window and clicked on one of the tables. To my horror got the dreaded "index out of range error". I've only seen this before when using ranking measures which I definitely wasn't. i then loaded up the same workbook on another pc running Excel 2013..no problem at all. I tried all the usual trick reinstalling office and no joy.

Fortunately i keep all my workbooks on dropbox so would back 30 versions until I found one that didn't have this error. I then started adding in things to see if I could break it. I discovered the weirdest issue. It's just to do with freezing columns!!!!! However it only seems to happen if there is a calculated column amongst the frozen columns. I can't work out exactly what does it as seems to be a combination of selecting several calculated columns, freezing and then moving them around.

Certainly if you have this issue just unfreeze the columns on the offending tab. But this is nuts coding!!!!

hopefully this will save others hours of their life trying to work out what they did wrong.

Mike

 

[scottsen]

Thanks for the heads up. I did send this off to somebody at Microsoft...

 

[masplin]

Hi Scott

I have actually had to abandon 2016 and go back to 2013 as couldn't get any of my sql or even linked tables to update. The linked table was only 40 rows. Is there somewhere to report these things directly to Microsoft as was really looking forward to using 2016 as 2013 is so poor?

Mike