none
Nested IF statement to ignore certain cells based on text RRS feed

  • Question

  • Hi All,

    I have a formula that populates a spreadsheet with managers rosters codes.  Not only does it do this, but it also checks a defined name list (MRC) to see if the managers roster code is a management code, or a regular staff code.  When it finds a manager roster code, it enters Mgr into the cell, instead of the specific code (they're over 120 codes, and they're irrelevant for what I want from this database, and only serve to clutter it up making it hard to read).  The code is reproduced below:

    =IFERROR(VLOOKUP($C133,'[ManagementRoster.xlsm]ManagementRoster'!$E$23:$FL$800,COLUMNS($E:CY),FALSE),"Free")

    The problem is, this erases annual/other leave, and enters "free", as it does not recognise leave codes as management codes. and obviously, I don't want leave to show up as Mgr, because they're not! they're on leave!

    I can't figure out how to nest another if statement in there.  Basically, when reading the roster, if the formula finds: "AL" "AT" "TR" "TT" "SEC" "LSL" then it needs to reproduce them as they're found in the roster.  If it finds anything else, it needs to check if its management code, and show Mgr.  If its anything else, it needs to show "Free".

    Help me please! this is doing my head in! :)

    Thanks guys!

    Tuesday, December 3, 2013 6:47 AM

Answers

  • For any future Excel/VBA'ers who stumble across this, I resolved this with the use of helper rows.

    Its slightly sluggish (I run it on ~400 rows, I can't imagine excel would be pleased if you ran this on more than a few thousand), but its functional.

    Above the row you want the Vlookup result, insert this formula:

    =IFERROR(VLOOKUP($C131,'[Management ROSTER.xlsm]Management Roster'!$E$23:$FL$800,COLUMNS($E:CY),FALSE),"")

    This row will eventually be hidden, so don't worry too much about it.

    In the row you want the 'filtered' codes, insert this formula:

    =IF(ISNUMBER(MATCH(E130,MRC,0)),"Mgr",IF(COUNTIF(Leave_Types,E130),E130,"Free"))

    
    This will look at the value your Vlookup formula has returned, check if it matches the MRC defined name list, and if it matches, will return "Mgr". If it doesn't match the MRC list, it will check the defined name Leave_Types. If it matches, it will enter the value your Vlookup formula returned (preserves the Vlookup data basically, duplicating it in the row below). If there is no match to the MRC list, or the Leave_Type list, it will enter "Free" as the cell value.
    Thursday, December 5, 2013 10:25 PM

All replies

  • Try

    =IF(OR($C133={"AL","AT","TR","TT","SEC","LSL"}), $C133, IFERROR(VLOOKUP($C133, '[ManagementRoster.xlsm]ManagementRoster'!$E$23:$FL$800, COLUMNS($E:CY), FALSE), "Free"))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 3, 2013 7:25 AM
  • Hans, my seemingly ever-present VBA wizard.

    I have a massive apology, the code I produced above was not the correct code I needed tweaked.  I have tried adapting your answer to my correct code but can't make it work.  I'll produce both below, and perhaps I could beg another solution from your amazing skills

    Correct Code I need tweaked:

    =IFERROR(IF(COUNTIF(MRC,VLOOKUP($C131,'[ManagementRoster.xlsm]ManagementRoster'!$E$23:$FL$800,COLUMNS($E:CY),FALSE)),"Manager","Free"),"")

    And my attempt to add your correction to this code, which still correctly shows "Manager" when it finds a management code, but is still incorrectly displaying AT/AL/TT etc as "free"

    =IF(OR($C131={"AL","AT","TR","TT","SEC","LSL"}),$C131,IFERROR(IF(COUNTIF(MRC,VLOOKUP($C131,'[ManagementRoster.xlsm]ManagementRoster'!$E$23:$FL$800,COLUMNS($E:CY),FALSE)),"Manager","Free"),""))

    Hopefully you can help me, I have no idea why it's not working! :)
    
    
    Tuesday, December 3, 2013 10:21 PM
  • The two formulas aren't equivalent - you're using different file names, sheet names and values...

    I can test that much, since the entire IFERROR part is ignored if C131 is one of the values in the list "AL","AT","TR","TT","SEC","LSL".

    Are you sure you're working with the correct cells?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 4, 2013 1:05 AM
  • Oh my goodness I forgot to scrub my data before posting online.

    I hope nobody saw that.  or those URL's.  I feel very silly now.

    I have fixed the discrepancies Hans, the two formulas now look at the 'same' sheet/file names

    EDIT: I should add, that I don't want the entire IFERROR function ignored if C131 is true for those values, I only want it to skip the MRC "Mgr" "Free" part. 

    I may be asking the impossible?

    2nd Edit: If I'm asking the impossible, can I define more than one named list? then I could just make a defined name for each value I want maintained such as AL, AT, TR etc

    Wednesday, December 4, 2013 1:41 AM
  • 1) I don't understand this.

    2) You can replace {"AL","AT","TR","TT","SEC","LSL"} with a (named) range. You must then confirm the formula with Ctrl+Shift+Enter to make it an array formula.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 4, 2013 7:05 AM
  • The problem with the named range option is I already have a named range - 'MRC'.  So when the Vlookup formula finds matches, it checks it against a list of Manager roster codes (ABC12, DEF13, HGF41), and if its a match, it only shows 'Mgr' in the cell, instead of the original roster code.  If its not a match for anything in the defined name range MRC, it shows a blank cell (because the staff member is not a manager for that week). 

    But this erases their leave (those are the codes, AL, AT etc) because they don't match the MRC defined name list.  But i can't just add them to the MRC, because then they'll match and show Mgr when they're on leave (and while they're dedicated, I don't think they're coming in from the their holidays to manage :P)

    Wednesday, December 4, 2013 10:19 PM
  • My idea was to create a separate named range containing the leave codes.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 4, 2013 10:32 PM
  • I understand that part, but then it skips the Vlookup entirely, because the first formula returns a 'false', and stops there.  I still need it to run the vlookup, I just don't want the vlookup to return anything other than manager codes (MRC defined name) and Leave codes.

    Wednesday, December 4, 2013 10:50 PM
  • I'm afraid you've lost me. I thought that if it's a leave code, it cannot be anything else...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, December 4, 2013 11:18 PM
  • 
    

    That is correct, I'll try to list what I think is happening and see where it gets us

    in the original formula I posted (the very first post) I have a simple Vlookup formula.  It looks up the data from another spreadsheet (the roster) finds the staff members unique number (stored in C133) and returns the columns from E through CY for that row.  It reproduces the entire roster.

    I wanted to modify this so it only 'vlookup'ed management codes.  This lead to the formula which is the second formula in my second post.  From my understanding, this changed the original formula so it would find the unique number (c133), and prior to returning the columns E-CY it would check to see if the cell value it was returning matched any values stored in the defined name list MRC.  If it did, it would return 'Manager' instead of the actual roster code (which is formated something like ABC12, AFT33) etc.  The exact code doesn't matter for this spreadsheet, it only matters if they're a manager.  If the cell value found did not match the MRC list, it would return "free", telling me this staff member is not currently a manager for that roster week (one column = one week).

    The problem is that there are leave codes (AL, AT etc) that are present in the roster.  My new formula discards these, as they are not in the MRC defined name list.  I can't add them to the MRC list, because then they'd show as 'Manager', and because they're not on the MRC list, they show as "free" when they're not - they're on leave.

    Wednesday, December 4, 2013 11:37 PM
  • For any future Excel/VBA'ers who stumble across this, I resolved this with the use of helper rows.

    Its slightly sluggish (I run it on ~400 rows, I can't imagine excel would be pleased if you ran this on more than a few thousand), but its functional.

    Above the row you want the Vlookup result, insert this formula:

    =IFERROR(VLOOKUP($C131,'[Management ROSTER.xlsm]Management Roster'!$E$23:$FL$800,COLUMNS($E:CY),FALSE),"")

    This row will eventually be hidden, so don't worry too much about it.

    In the row you want the 'filtered' codes, insert this formula:

    =IF(ISNUMBER(MATCH(E130,MRC,0)),"Mgr",IF(COUNTIF(Leave_Types,E130),E130,"Free"))

    
    This will look at the value your Vlookup formula has returned, check if it matches the MRC defined name list, and if it matches, will return "Mgr". If it doesn't match the MRC list, it will check the defined name Leave_Types. If it matches, it will enter the value your Vlookup formula returned (preserves the Vlookup data basically, duplicating it in the row below). If there is no match to the MRC list, or the Leave_Type list, it will enter "Free" as the cell value.
    Thursday, December 5, 2013 10:25 PM