+ Reply to Thread
Results 1 to 6 of 6

Add IF IS BLANK argument to existing formula

  1. #1
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    155

    Add IF IS BLANK argument to existing formula

    Hello,

    I would like to add an "IF IS BLANK" argument to this existing formula: =IF(MAINDATA!A2<>"",MAINDATA!A2,"")

    The attached sample spreadsheet contains data in the MAINDATA worksheet bringing over certain columns to the VENDOR_REPORT worksheet.

    Now we decided that we only want rows where the RELEASED column (COLUMN Q in the MAINDATA worksheet) are blank.

    We don't want to use VBA to do this. Can I simply add an IF IS BLANK argument to the existing formula, and if so, what would that look like?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: Add IF IS BLANK argument to existing formula

    This is the basis of the formula

    in VENDOR REPORT A2
    =IFERROR(INDEX(MAINDATA!$A$2:$T$1000,AGGREGATE(15,6,ROW($A$2:$A$1000)/((ISNUMBER(MAINDATA!$Q$2:$Q$1000)=FALSE)),ROWS(A$2:A2))-(2-1),1),"")
    in B2
    =IFERROR(INDEX(MAINDATA!$A$2:$T$1000,AGGREGATE(15,6,ROW($A$2:$A$1000)/((ISNUMBER(MAINDATA!$Q$2:$Q$1000)=FALSE)),ROWS(A$2:A2))-(2-1),2),"")
    in C2
    =IFERROR(INDEX(MAINDATA!$A$2:$T$1000,AGGREGATE(15,6,ROW($A$2:$A$1000)/((ISNUMBER(MAINDATA!$Q$2:$Q$1000)=FALSE)),ROWS(A$2:A2))-(2-1),4),"")
    in D2
    =IFERROR(INDEX(MAINDATA!$A$2:$T$1000,AGGREGATE(15,6,ROW($A$2:$A$1000)/((ISNUMBER(MAINDATA!$Q$2:$Q$1000)=FALSE)),ROWS(A$2:A2))-(2-1),10),"")
    in E2
    =IFERROR(INDEX(MAINDATA!$A$2:$T$1000,AGGREGATE(15,6,ROW($A$2:$A$1000)/((ISNUMBER(MAINDATA!$Q$2:$Q$1000)=FALSE)),ROWS(A$2:A2))-(2-1),14),"")
    in F2
    =IFERROR(INDEX(MAINDATA!$A$2:$T$1000,AGGREGATE(15,6,ROW($A$2:$A$1000)/((ISNUMBER(MAINDATA!$Q$2:$Q$1000)=FALSE)),ROWS(A$2:A2))-(2-1),7),"")

    Column G is now redundant since it will always contain spaces

    NOTE: I'm not sure why zeroes are appearing, this is a standard formula I use with amended parameters to reflect your data. I've never had this problem before but the only condition set ifor column Q to be blank which it looks like they are.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Valued Forum Contributor
    Join Date
    06-22-2018
    Location
    Blackpool, England
    MS-Off Ver
    2019
    Posts
    408

    Re: Add IF IS BLANK argument to existing formula

    That is cool, but my head hurts trying to figure out what it's doing!

    Tim
    Never stop learning!
    <--- please consider *-ing !

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: Add IF IS BLANK argument to existing formula

    AGGREGATE 15 is equivalent to SMALL

    I used to have a detailes explanation of how this works but cant find it.

    If you search for remove blanks from list or INDEX SMALL it should turn up lots of versions of this formula - some with explanation.
    I'm afraid I'm at work so haven't got time to explain it fully.

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,021

    Re: Add IF IS BLANK argument to existing formula

    Please try at

    A2
    =IFERROR(INDEX(MAINDATA!$A:$A,AGGREGATE(15,6,ROW(MAINDATA!$Q$2:$Q$99)/(MAINDATA!$Q$2:$Q$99=0)/(MAINDATA!$A$2:$A$99>0),ROWS(A$2:A2))),"")

    B2:F2
    =IF($A2="","",VLOOKUP($A2,MAINDATA!$A$2:$T$39,MATCH(B$1,MAINDATA!$A$1:$T$1,),))

    E2
    =IF($A2="","",IFERROR(1/(1/VLOOKUP($A2,MAINDATA!$A$2:$T$39,MATCH(E$1,MAINDATA!$A$1:$T$1,),)),""))


    or if you have update Office 365, Dynamic array should available
    A2
    =FILTER(MAINDATA!$A$2:$A$99,(MAINDATA!$Q$2:$Q$99="")*MAINDATA!$A$2:$A$99)
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    155

    Re: Add IF IS BLANK argument to existing formula

    Thank you Special-K and Bo-Ry as your formulas worked perfectly. I did try to use the Office 365 option (see below), but am not sure why it doesn't work. I have used the 365 versions of formulas before. That said, the other formulas you both provided work. Thank you so much!

    =FILTER(MAINDATA!$A$2:$A$99,(MAINDATA!$Q$2:$Q$99="")*MAINDATA!$A$2:$A$99)

+ 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] Add IF 0 RETURN BLANK to existing formula
    By Shell-in-Melb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-24-2020, 08:45 AM
  2. How to add another argument to existing formula
    By buck08 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-23-2018, 03:05 PM
  3. [SOLVED] Add to existing match index formula a formula that will leave cells blank
    By Hedy in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-15-2017, 02:34 PM
  4. [SOLVED] amending existing formula to blank when there is no data
    By chumster in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-30-2017, 04:03 AM
  5. [SOLVED] amending existing formula to return blank when there is no data
    By chumster in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-30-2017, 03:30 AM
  6. [SOLVED] Get existing Formula to return blank instead of 0 please
    By fruit&veg in forum Excel General
    Replies: 4
    Last Post: 01-22-2016, 07:53 AM
  7. Replies: 4
    Last Post: 10-21-2015, 11:37 PM

Tags for this Thread

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