#SPILL Error

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,600
Office Version
  1. 365
Platform
  1. Windows
I am using the FILTER function to filter out any 0 values in a list.

The range for the FILTER is dynamic and most of the time it returns the filtered list but sometimes it returns #SPILL
1708695297813.png


The formula being used is
Excel Formula:
=FILTER(L5:OFFSET(M5,L3-1,0),M5:OFFSET(M5,L3-1,0)>0)
1708695314579.png


When there are some 0 values it seems to work
1708695405399.png





TIA
 

Attachments

  • 1708695223392.png
    1708695223392.png
    7 KB · Views: 2

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Checked this...all cells below and to the right of the formula are blank
 
Upvote 0
Why not just use
Excel Formula:
=FILTER(L5:M1000,M5:M1000>0)
Rather than using volatile functions.
 
Upvote 0
How do I turn on the error handler/message as I can't see it?
1708696240346.png
 
Upvote 0
I am pretty sure that:
Excel Formula:
L5:OFFSET(M5,L3-1,0)
is not a valid range reference.
 
Upvote 0
Why not just use
Excel Formula:
=FILTER(L5:M1000,M5:M1000>0)
Rather than using volatile functions.

OK, so I thought I had tried this and also implemented a SORT into the same formual and it seems to return a list including the blanks but doesn't seem to do that now.....

I will try using the static range and see how that goes.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,215,073
Messages
6,122,970
Members
449,095
Latest member
Mr Hughes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top