Forum Discussion

JimG12345's avatar
JimG12345
Copper Contributor
Feb 15, 2024

spill errors using =row()

This is an example of the issue I am having. This equation works in pre excel 365 versions =ROW(kW!DE205:DE326)+MATCH(DE46,kW!DE205:DE326,0)-1

but now that I have office 365 I get a spill error on the same equation

if I add a @ in front of ROW I do not get a spill error but it tremendously complicates things for me as my XLSM is very complicated. 

=@ROW(kW!DE205:DE326)+MATCH(DE46,kW!DE205:DE326,0)-1

 

I teach a class where I share my spreadsheets, and am getting many spill errors.

I want to share my pre-365 XLSMs with my students.  But those pre-365 XLSMs produce many spill errors in 365.XLSMs.  So how can I find compatibility with pre-365 XLSMs?

 

Thanks 

 

2 Replies

  • JimG12345 

    Since Excel in Microsoft 365 treats all formulas as array formulas, ROW(kW!DE205:DE326) returns 124 row numbers. Can't you use

     

    =ROW(kW!DE205)+MATCH(DE46,kW!DE205:DE326,0)-1

Resources