+ Reply to Thread
Results 1 to 4 of 4

#spill! error using Filter function

  1. #1
    Registered User
    Join Date
    06-12-2021
    Location
    Montreal, Canada
    MS-Off Ver
    Office 365
    Posts
    49

    #spill! error using Filter function

    Hello,

    I have this formula in cell AN2,

    =IFERROR(FILTER(NameGiftsUnique!$D:$I,NameGiftsUnique!$B:$B=D2,""),"")

    if the cell D2 is empty, I am getting a #SPILL! warning but the correct value is showing if the cell D2 is populated. I am using filter to remove the #SPILL! but if I could prevent the message from occurring. It would be more productive.

    Tx for your help,

    Yves
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,528

    Re: #spill! error using Filter function

    Why not just test D2 first? =IF(D2="", "", FILTER(…)
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    06-12-2021
    Location
    Montreal, Canada
    MS-Off Ver
    Office 365
    Posts
    49

    Re: #spill! error using Filter function

    Thank you TMS, I made the change and it works flawlessly.

    Tx

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,528

    Re: #spill! error using Filter function

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help with #SPILL! error
    By BH1983 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-28-2021, 07:49 PM
  2. [SOLVED] Spill error for index aggregate function
    By whahnelt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-12-2021, 08:38 AM
  3. Replies: 5
    Last Post: 01-27-2021, 12:07 PM
  4. Using UNIQUE function with table ranges, cant avoid #SPILL! error
    By Lacaycer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-22-2020, 09:38 AM
  5. [SOLVED] Excel Filter function matching to a Spill Array
    By ibuhary in forum Excel General
    Replies: 16
    Last Post: 10-25-2020, 11:05 PM
  6. [SOLVED] Filter Causing SPILL# in IF Formula
    By Fugdkn in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-18-2020, 06:29 AM
  7. Replies: 3
    Last Post: 08-09-2020, 10:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1