+ Reply to Thread
Results 1 to 7 of 7

if, index, small - formula issue

  1. #1
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,189

    if, index, small - formula issue

    hello

    here is my formula

    {=IFERROR(INDEX(INDIRECT("Master!"&F$6&":"&F$6),SMALL(IF(((INDEX(_Data,0,1))='Missed Accounts'!$D$9)*((INDEX(_Data,0,9))=" - ")*((INDEX(_Data,0,5))=RIGHT($D$11,LEN($D$11)-SEARCH(" ",$D$11))),ROW((INDEX(_Data,0,9))),""),ROW()-14)),"")}

    it essentially filters by "billing cycle", employee name and shows me the data.

    the issue is, in cell D11 (missed account - sheet), my date is "july 7th...the formula is only filtering by "7th" and not by july. for example, in the "master" sheet, if you filter by "test1" - column C ; "7th" - column G ; "-" in july - column AI, you should get 3 names, however in my attached, it is only getting one name, as the formula is not dependent on "july 7th"

    can someone pls help?

    see sample attached.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,189

    Re: if, index, small - formula issue

    any thoughts on this...thx u

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: if, index, small - formula issue

    from the looks of it, this part of your formula is restricting the "date" to last 3 or 4 characters?

    =RIGHT($D$11,LEN($D$11)-SEARCH(" ",$D$11))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: if, index, small - formula issue

    duplicate post
    Last edited by FDibbins; 10-24-2012 at 01:26 AM.

  5. #5
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,189

    Re: if, index, small - formula issue

    yes you are right.

    in the "master" sheet, the months are located in row 7 and the billing date is in range F9 down.

    I am not sure how to integrate the two together in the formula?

    ur thoughts...thx u

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: if, index, small - formula issue

    maybe if you used actual dates, you could simplify your formulas somewhat?

  7. #7
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,189

    Re: if, index, small - formula issue

    would it possible to include that portion in my formula??

    someone pls help!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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