Results 1 to 4 of 4

Thread: Help with populating a cell depending on several criteria

  1. #1

    Help with populating a cell depending on several criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am trying to populate a cell with a forumla depending on organizational data (eg. employee's job title, division, department).


    I color coded the names/job titles so you can see what I am trying to accomplish, and highlighted the text in red where I think will be confusion in the formula without creating some type of IF/AND/OR exception statement? (Eg. can I have 2 different departments under the same division pull the correct manager?)


    Any help would be greatly appreciated.

    Thank you all for your time!
    Attached Files Attached Files

  2. #2
    If this is not clear:

    Problem #1: The Division is the top of the organization, each Division has 1 or more Departments. The employees in the same department with the same "ReportsTo" title should populate the employee's name with that job title in the same Department. (eg. Kelley is in the Office Department and reports to the Department Manager, who is Bob.)

    Problem #2: Bob (Office Department Manager) reports to Jane (Controller), however Jane's Department is "Finance" not "Office". Is this possible to do?

    Problem #3: Jane reports to Heather (CEO) who is neither in the same Division or Department altogether.

    Hope that helps if I was unclear.

    Thanks!

  3. #3
    Hi,
    I'm not 100% clear but I think that your issue is that you don't have a hierarchy in the spreadsheet I think.
    If would be tempted to approach this by putting people in a hierarchy so.
    The CEO is a 1.
    All 2's report to a 1, the CEO
    All 3's report to a 2. If they are a 3 in XXXX department then they report to this 2 and so on.

  4. #4

    possible approach

    I noodled around with this for a bit and with a few helper columns was able to return the name of the supervisor. Not very flexible but maybe it will get you started. The internal sales associate doesn't work since Tara is only the boss of external but this could be fixed with something like I did for the Controller. Let me know if you have questions.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •