Help putting data in the correct column automatically
Hello.
I'm working with a worksheet where I'm trying to put a Workers management team into the correct column based on the Managers Management Level. I'm able to see the flow of an organization by using an INDEX(MATCH()) formula (=INDEX({Manager}, MATCH(Worker@row, {Worker}), 1), but how do I get the data to show up in the correct column?
In the example below, the data highlighted should be showing up in the Vice President / ML3 columns.
Any help would be greatly appreciated.
Answers
-
I feel like I need an additional piece of criteria in the Formula for it to know where to place the data when it's found.
-
You might need an INDEX/COLLECT formula to parse out multiple criteria to fill the columns.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
-
@Michelle Choate 2 Will you please give me an example on how to write this formula based on my example above? I've looked at the Formula, but I can't figure out how to use it correctly.
-
I would have to see your current formula and the references that you have in order to help you rewrite it. Can you please provide what it looks like? Also happy to jump on a zoom call whenever if you wanted to go over it in person.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
-
@Michelle Choate 2 I posted the formula along with the image above.
Thank you for the assist. ❤️
-
It would be something along the lines of
=INDEX(COLLECT({Manager}, {Worker}, @cell = Worker@row, {Level}, @cell = "Vice President"), 1)
-
Thank you @Paul Newcome I'll give that a try.
-
@Paul Newcome Hello again.
Thank you, I think I'm getting closer.
If I have/need to add an Employee ID to identify the Vice President, how would I do that with this function?
=INDEX(COLLECT({Manager}, {Worker}, @cell = Worker@row, {Level}, @cell = "Vice President"), 1)
I have instances with Employees who have the same name, so I need to use the Employee ID to identify the correct chain of command (otherwise, it defaults to the first instance's chain of command). I tried swapping out {Manager} for {Manager ID}, but that didn't work like I expected it to (see below).
=INDEX(COLLECT({Manager ID}, {Worker}, @cell = Worker@row, {Mgmt Level}, @cell = "Director"), 0)
I keep getting an "Invalid Column Value" error for both of the above functions. Any thoughts on how I can overcome this?
-
Do you have some additional screenshots for context?
-
Yes, it would be just like the one above, but I've added the Employee ID at the beginning for the Worker. I'm wondering if I should be adding it for Team Lead, and the other management levels, as well.
-
What does your source data look like?
-
I have all of this data in one sheet.
I couldn't screen capture it all in one graphic, so I needed to send you two. Is this what you are asking for?
The Manager will also show up as a Worker, with their Management Level for them as well (which could range from Team Lead to CEO), and their Manager ID will be their Employee ID (ID's are assigned once to all employees and DO NOT change for the duration of employment).
I appreciate the assistance. ❤️
-
Ah. Ok. If it is all in one sheet, you will need to manually add the IDs for each level.
-
They already exist, and it's still not working for me. Not sure what I'm doing wrong.
-
Hello @Mariann Carmen. If you want to jump on a quick zoom to see what is going on, feel free to message me via email michelle.choate@outlook.com :) Happy to take a peak at your formulas and troubleshoot with you.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!