Trying to create a Level to CEO hierarchy without any success

Verity Harries

Hi there

I am trying to create a formula to return a value of 0, -1, -2, -3 etc. depending on the employee's level to the CEO. It's easy enough to get the first two levels where Direct Manager = 10002 then Level to CEO = -1, but how do I get anyone reporting into the -1 group to have a -2 and so on until -5?

Thanks and looking forward to the answer!

Group People Id = 10001 OR 10002 OR 10004 then Level to CEO = 0. Anyone with 10001 OR 10002 OR 10004 IN Direct Manager Id, Level to CEO = -1. Anyone whose Direct Manager Id has a -1 in Level to CEO has a -2 in Level to CEO. Anyone whose Direct Manager Id has a -2 in Level to CEO has a -3 in Level to CEO. Anyone whose Direct Manager Id has a -3 in Leader Type has a -4 in Level to CEO. Anyone whose Direct Manager Id has a -4 in Level to CEO has a -5 in Level to CEO.

Andrée Starå

https://community.smartsheet.com/discussion/129724/trying-to-create-a-level-to-ceo-hierarchy-without-any-success

Hi,

I hope you're well and safe!

Can you share some screenshots? (Please delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Verity Harries

Hi Andrée,

Thanks so much for coming back so quickly! I've done these ones manually to show you what I am trying to achieve, I'd LOVE a formula for the Level to CEO column based on the employee hierarchy of who they are reporting into. CEO = 0, those into CEO = -1 and so on. Please let me know if you need any more clarification. Each employee is assigned a Group People ID Number and their manager is captured both by name and their ID, too, so I am hoping we can use those unique identifiers. I could do it all manually, however if someone changes manager, it would be another field that needs manual maintenance.

Brent C. Wilson

I haven't tested this

=IFERROR(IF(ISNUMBER(MATCH([Direct Manager ID]@row, [Group People ID]:[Group People ID], 0)),
INDEX([Level to CEO]:[Level to CEO], MATCH([Direct Manager ID]@row, [Group People ID]:[Group People ID], 0)) - 1,
0), 0)

Put that in the "Level to CEO" column and see if it works.

Note: If it cannot find the [Direct Manager ID] in the [Group People ID] then it assumes they must be the CEO and return a Zero.

• 1. Finding the Boss: The formula starts by looking for the "Direct Manager ID" in a list of "People IDs." Essentially, it's trying to find out who the boss is for the person in that row.

• 2. Checking if the Boss Exists: If it finds the boss's ID in the list, it checks what level that boss is from the CEO. This level is stored in another column called "Level to CEO."

• 3. Calculating the Level: Once the boss's level is found, the formula subtracts one from that level. This subtraction represents that the person in the row is one step below their boss in the company hierarchy.

• 4. Handling the CEO: If the formula can't find the boss's ID in the list, it assumes that the person in the row is the CEO (or at the top of the hierarchy). In this case, the level is set to "0," meaning they report directly to no one.

• » Final Result: The formula ultimately fills in the "Level to CEO" for the person in that row. This shows how many steps away they are from the CEO.