+ Reply to Thread
Results 1 to 8 of 8

Reference IF Formula

  1. #1
    Registered User
    Join Date
    05-03-2016
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    4

    Reference IF Formula

    What I am doing: I have two separate worksheets with multiple columns each (basic stuff); Pending and Disbursements. I want to set up a formula that will take a name from my Pending worksheet and input it in my Disbursement worksheet if it meets the criteria. That part I can do, no problem. Where I am having issues is when I fill the formula down, I start getting blank cells between names; and that I do not want.

    So what I am really trying to do is set up a formula that will fill the next matching criteria name in the next cell. Here is the last attempt of mine to do so: =IF([Pending.xls]May!$H10="Y", [Pending.xls]May!$C10, IF([Pending.xls]May!$H11="Y", [Pending.xls]May!$C11, IF([Pending.xls]May!$H12="Y", [Pending.xls]May!$C12, "")))

    The issue there is that I get two cells that fill the same name, and every third cell changes name (i.e. Smith, Smith, Jones, Brown, Brown, Denis, etc., etc.)

    Any suggestions (see bold/italics/underlined)? Thank y'all in advance!

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Reference IF Formula

    Welcome to the Forum.


    Can you post a sample workbook with sample data and a solved example or two?
    The solved cells can be typed in or hardcoded and you tell us you want a formula in that particular cell instead.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Registered User
    Join Date
    05-03-2016
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    4

    Re: Reference IF Formula

    Here is the sample. In actuality, they are separate workbooks completely, but they objective is still the same: the formula will go on the Disbursement worksheet referencing the Pending worksheet.

    My criteria: If "Overage" = Y on the Pending worksheet, then insert the corresponding "Name" into the Disbursement sheet. If "Overage"=N, then I want it test the next cell and so on and so forth.

    The only ways I have been able to set it up either leaves a blank cell between names on the Disbursement sheet or doubles up on a name (see example from original post).
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Reference IF Formula

    Why not use a pivot table as the basis then have your formulas in the pivot or off to the right?

    You can then filter your table to only have Overage = Y

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Reference IF Formula

    Hi, welcome to the forum

    That part I can do, no problem.
    And how are you doing that?
    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

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Reference IF Formula

    You dont seem to have provided that much info, but to extract unique names that meet your criteria, try this ARRAY formula...
    =IFERROR(INDEX(Pending!$C:$C,SMALL(IF(Pending!$H$3:$H$18="y",ROW(Pending!$A$3:$A$18)),ROWS($A$1:A1))),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Once you have that, you can use SUMIF() based on those names

  7. #7
    Registered User
    Join Date
    05-03-2016
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    4

    Re: Reference IF Formula

    I'm not completely sure what you mean? I can currently filter to only show Overage=Y.

  8. #8
    Registered User
    Join Date
    05-03-2016
    Location
    Texas
    MS-Off Ver
    2010
    Posts
    4

    Re: Reference IF Formula

    And how are you doing that?[/QUOTE]

    I am just using an IF formula: =IF(Overage=Y, Name, "")...to put it simply. But that is where I get the issue of having blank cells. Let me try your suggestion.

+ 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. Replies: 1
    Last Post: 02-11-2015, 01:56 PM
  2. [SOLVED] Locked formula changes reference when rows added to reference sheet in same workbook
    By macrorookie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-19-2014, 04:08 PM
  3. [SOLVED] Replace absolute cell reference with Indirect cell reference in formula
    By Roothy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-11-2013, 04:46 AM
  4. Dragging formula, change sheet reference, but not cell reference
    By Kalilaya0419 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2013, 04:50 PM
  5. Replies: 7
    Last Post: 02-03-2013, 06:25 PM
  6. Replies: 5
    Last Post: 09-21-2012, 02:08 PM
  7. Circular Reference when formulas reference end of row formula!
    By Spellbound in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-29-2009, 08:26 AM

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