4 Replies Latest reply on Oct 2, 2013 11:26 AM by Matt Lutton

    Need a way to use a dimension more than once in an If statement

    Kristen Rohlfing

      I have a workbook that requires a manager parameter which would ordinarily be no problem but this one is tricky.  To be able to do this, I had to take each manager's name and manually assign which organization they work for in an If, Then calculation.  Here's a simple example of the formula for reference:

       

      If [Organization] = "Paper"

           or [Organization] = "Cardboard"

           Then "Jane"

      ElseIf [Organization] = "Plastic"

           Then "Joe"

      ElseIf [Organization] = "Construction Paper"

           or [Organization] = "Card Stock"

           Then "Fred"

      End

       

      This worked great for what I needed until they decided to throw a wrench at me.  I was then told that a new member of the team was added, lets call him Tim, and he wanted visibility into all of these organizations at the same time, while still allowing each individual team member listed above to still see the information relevant to them.  I can no longer do an If, Then statement because after assigning each organization to the team members, you can not then assign the organizations again to Tim. Just to try to explain myself better, I mean cannot do this:

       

      If [Organization] = "Paper"

           or [Organization] = "Cardboard"

           Then "Jane"

      ElseIf [Organization] = "Plastic"

           Then "Joe"

      ElseIf [Organization] = "Construction Paper"

           or [Organization] = "Card Stock"

           Then "Fred"

      ElseIf [Organization] = "Paper"

           or [Organization] = "Cardboard"

           or [Organization] = "Plastic"

           or [Organization] = "Construction Paper"

           or [Organization] = "Card Stock"

           Then "Tim"

      End

       

      Does anyone have a suggestion on a way to tackle this problem without adding another parameter?

       

      Any suggestions are greatly appreciated.