+ Reply to Thread
Results 1 to 15 of 15

IF Function....can you move to the next instead of just leaving blanks?

  1. #1
    Registered User
    Join Date
    05-02-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    34

    IF Function....can you move to the next instead of just leaving blanks?

    I am trying to make gathering/Sorting and graphing some information a tad bit easier, so I wanted to create a table that had everything that I needed using if functions....I think I have that down, but I am ending up with a long list with a ton of blanks because not everything meets the criteria.....is there a way to eliminate the blanks and just move to the next cell if it doesn't work?

    Example:

    =IF(AND(Table!A3="Regional South",Table!R3="Pre P&L"),Table!CA3,"")
    =IF(AND(Table!A3="Regional South",Table!R3="Post P&L"),Table!CA3,"")
    =IF(AND(Table!A3="Major North",Table!R3="Pre P&L"),Table!CA3,"")
    =IF(AND(Table!A3="Major North",Table!R3="Post P&L"),Table!CA3,"")

    I am wanting to check two criteria, and if those are met, pull the data from another cell....if they aren't met, then it is currently leaving it blank. Instead of having a list with blanks in it, I am wanting to maybe have it skip to the next cell....is there a way to do this?

    ie this:

    .86
    .53
    .78
    .96
    .45

    instead of this:

    .86


    .53
    .78


    .96



    .45

  2. #2
    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: IF Function....can you move to the next instead of just leaving blanks?

    Hi and welcome to the forum

    without seeing some sample data that you are working with, giving some suggestions is a bit hard?
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    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

  3. #3
    Registered User
    Join Date
    05-02-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: IF Function....can you move to the next instead of just leaving blanks?

    Okay, working on that now, thanks

  4. #4
    Registered User
    Join Date
    05-02-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: IF Function....can you move to the next instead of just leaving blanks?

    IF(AND()).xlsx

    Here is the attachment.....I basically want the data to not have the gaps that they have...maybe it requires some sort of programing that is more advanced than what I am using?
    Last edited by PrncssJ; 03-24-2013 at 07:42 PM. Reason: More Clarification

  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: IF Function....can you move to the next instead of just leaving blanks?

    See if the attached will work for you?

    Note in N4 and P4, you did not have a space btw PRE P&L and POST P&L
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-02-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: IF Function....can you move to the next instead of just leaving blanks?

    What you did looks like it will work for me.

    Thanks for pointing out the errors....I kinda quickly put it together so you could get the gist of what I was trying to say.

    I'm trying to sort out what Match() Mid() Left() Row() do to the formula....is there a brief explanation you can give? I will be having to manipulate this for other regions and want to make sure that I have the concept correct.

    Thanks tons!

  7. #7
    Registered User
    Join Date
    05-02-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: IF Function....can you move to the next instead of just leaving blanks?

    =IFERROR(INDEX($A$1:$E$20,MATCH($M$3&MID(M$4,5,99)&ROW($A5),$A$1:$A$20&$B$1:$B$20&$E$1:$E$20,0),MATCH(LEFT(M$4,3),$A$1:$E$1,0)),"")

    So this is what I got so far:INDEX($A$1:$E$20=You are looking from A1:E20 (A square), MATCH($M$3=instead of using a cell for index you are instead wanting to match what's in M3(Regional South) based off MID(M$4,5,99)= cell M4 starting at the 5th letter (I don't understand the 99) &ROW($A5)

  8. #8
    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: IF Function....can you move to the next instead of just leaving blanks?

    what Im doing there is...

    =IFERROR(INDEX($A$1:$E$20,MATCH($M$3&MID(M$4,5,99)&ROW($A1),$A$1:$A$20&$B$1:$B$20&$E$1:$E$20,0),MATCH(LEFT(M$4,3),$A$1:$E$1,0)),"")

    MATCH($M$3&MID(M$4,5,99)&ROW($A1),$A$1:$A$20&$B$1:$B$20&$E$1:$E$20,0)

    the $M$3 is a drop=down I put in there for you for flexibility - I figure this is a sample, your actual data will have info dfor all categories, so now you just select from the drop-down
    MID(M$4,5,99) is just looking at the heading in M4 and pulling out the info from the 5th character - for the next 99 characters (far more than you need, just a safety thing in case the text gets longer)
    ROW($A1) - now, this is where the selection takes place. you will note the formula in E =IF(C2="","",COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,"<>"))...this is sequentially numbering each category. ROW($A1) returns 1, the next row down will be ROW($A2) = 2, so this value, added to the other 2 returns, will give the 1st instance of the match (Regional South&Post P&L) & 1, the next row will look for the combo of Regional South&Post P&L & 2 etc

  9. #9
    Registered User
    Join Date
    05-02-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: IF Function....can you move to the next instead of just leaving blanks?

    Yeah, it's a sample and not the actual data. I sent you a direct message, maybe yo u can see if I am going in the wrong direction with what I am trying to do?

  10. #10
    Registered User
    Join Date
    05-02-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: IF Function....can you move to the next instead of just leaving blanks?

    I've uploaded the file I am working with.....I know it looks a mess. The "table" tab is where the original data is located. It has the Delivery Team (MN,MS,RN,RS are all that I report on) and the Pre/Post Column by month....which I use to determine which CPI/SPI values I use in the graphs (which are the last 18 tabs)

    Where I tried to implement your formulas are on the "sheet 23" located after the "table" sheet. In the end I think I will wanted to reference the "table" table and make a page so that I can base the graphs off of this tab instead of going through the tedious sorting process and individually clicking each cell......let me know if what I am thinking looks like it makes sense, or if there is something better you can recommend.

    Thanks

    PS-Looks like the formatting I had on the table sheet is gone, hopefully it's not too hard to read
    Attached Files Attached Files

  11. #11
    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: IF Function....can you move to the next instead of just leaving blanks?

    from your PM...
    I will be using the data to make graphs.....in the past I was going through and sorting the table page by, Regional North, Regional South, Major North, Major South......then by the Pre/Post P&L and then grabbing the CPI/SPI results to make graphs......like 16 of them! Sorting works okay, but it's time consuming, I am going to be handing this task over to another person and wanted to see if I can simplify the process a bit for her.

    Let me know what you think, does the method you showed me seem like it will be the best option for what I am doing?
    1st, the formulas I used will work on un-sorted data. You could probably set up your chart based on the table I provided, and just by changing the value from the drop-down in M3, your chart will change automatically.

    To adapt my formulas for your (larger) real data ranges, all you need to do is adjust the ranges - you can do this manually (by typing them) or using the mouse (delete the range in the edit window, and use the mouse to "point" to the new range)

    NOTE however, that the formulas I used are ARRAY formulas, you MUST enter them using CTRL SHIFT enter (they will have {} wrapped around them if done correctly)

  12. #12
    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: IF Function....can you move to the next instead of just leaving blanks?

    deleted duplicate post

  13. #13
    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: IF Function....can you move to the next instead of just leaving blanks?

    OK I took at look at sheet23, the mistake you made was to not enter using CTRL SHFT enter - they are array formulas. You just need to do that for the 1st 1, you can then copy/paste as normal

    I did notice that you have over 50 sheets (which in itself is no problem), and that most are charts, and that you have 16 sheets/charts just for various options for "Jan 13" Would it be easier if you put them all on a sheet called Jan 13? You could then just scroll around that single sheet to see the chart you wanted?

  14. #14
    Registered User
    Join Date
    05-02-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    34

    Re: IF Function....can you move to the next instead of just leaving blanks?

    Having all of the graphs on one page is a great idea......I think that's what I will try to do.

    Will it be as simple as copy/pasting the charts into the sheet I want? Right now I am having to go through each graph individually to change where the data is being pulled from (for each data point) because I copied the graph pages from another workbook sheet i was working on and the data points are still referencing that sheet, it's a heck of a mess. (I tried find and replace, but it doesn't work for what I was trying to do)

    I haven't had a chance to try the array in there yet, but I have looked at a tutorial, and I see how it works


    Quote Originally Posted by FDibbins View Post
    OK I took at look at sheet23, the mistake you made was to not enter using CTRL SHFT enter - they are array formulas. You just need to do that for the 1st 1, you can then copy/paste as normal

    I did notice that you have over 50 sheets (which in itself is no problem), and that most are charts, and that you have 16 sheets/charts just for various options for "Jan 13" Would it be easier if you put them all on a sheet called Jan 13? You could then just scroll around that single sheet to see the chart you wanted?

  15. #15
    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: IF Function....can you move to the next instead of just leaving blanks?

    Yes, you should be able to copy/paste, but also try cut/paste, it will remove the 1st copy, so you dont have to go back and delete - it will also retain the cell references as well

+ 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