PlusBob
New Member
- Joined
- Feb 16, 2016
- Messages
- 15
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
- MacOS
- Mobile
- Web
So here's a mock short version of what I have.
Sheet 1 has a lot of personal and work related information. On other sheets I have products that are saved as PDFs for the masses. This example on Sheet 2 is a recall roster, in flow chart form (imagine lines and arrows showing hierarchy, etc.)
I want to be able to not really touch (make it automated). I want it to match the teams, then list the employees, and then the formula below to match the cell# to the employees.*
In cells like G10:G12, I use the following formula, which works great to match the employees and then their cell#.
=IFNA(INDEX(Employees!$A$5:$A$22,MATCH(F10,Employees!$D$5:$D$22,0)),"")
Thank you for taking the time to invest in my inquiry. I've been working on so many different options, I'm stumped.
Sheet 1 has a lot of personal and work related information. On other sheets I have products that are saved as PDFs for the masses. This example on Sheet 2 is a recall roster, in flow chart form (imagine lines and arrows showing hierarchy, etc.)
I want to be able to not really touch (make it automated). I want it to match the teams, then list the employees, and then the formula below to match the cell# to the employees.*
Example would be (in Sheet 2 Columns E to G), a formula is inserted in F10-F12 that matches F9 "A" (Team), and list all of the employees**.
*I'm restricted in not using Macros and VBA due IT security policies (joy), so need a formula workaround.
**Each team has a set number of positions (billets). So for example A Team has up to 3 peeps in their team. So I would copy & paste the formula into F10-F12.
In cells like G10:G12, I use the following formula, which works great to match the employees and then their cell#.
=IFNA(INDEX(Employees!$A$5:$A$22,MATCH(F10,Employees!$D$5:$D$22,0)),"")
Thank you for taking the time to invest in my inquiry. I've been working on so many different options, I'm stumped.
A | B | C | D | E | F | G | H | I | J | |
1 | ||||||||||
2 | On Sheet 1 | Employees List | On Sheet 2 | Recall | ||||||
3 | ||||||||||
4 | Name | Teams | Work # | Cell # | Boss | |||||
5 | Cassidy | C | (303) 555-8002 | (720) 777-3023 | ||||||
6 | Dial-Tone | C | (303) 555-8987 | (720) 777-6400 | Asst Boss | |||||
7 | Duke | B | (303) 555-4200 | (720) 777-4808 | ||||||
8 | Face | A | (303) 555-7739 | (720) 777-7967 | Teams | Teams | Teams | |||
9 | Flagg | B | (303) 555-6809 | (720) 777-1216 | A | C | E | |||
10 | Flint | D | (303) 555-1887 | (720) 777-2410 | Face | (720) 777-7967 | ||||
11 | Frankel | E | (303) 555-2525 | (720) 777-0545 | Hannibal | (720) 777-9836 | ||||
12 | Greer | E | (303) 555-9358 | (720) 777-3421 | Snake-Eyes | (720) 777-2739 | ||||
13 | Gung-Ho | F | (303) 555-2178 | (720) 777-9765 | ||||||
14 | Hannibal | A | (303) 555-4427 | (720) 777-9836 | B | E | F | |||
15 | Hawk | B | (303) 555-4067 | (720) 777-4430 | ||||||
16 | Lady Jaye | D | (303) 555-5217 | (720) 777-3268 | ||||||
17 | Lifeline | E | (303) 555-4792 | (720) 777-5181 | ||||||
18 | Mainframe | C | (303) 555-3695 | (720) 777-5408 | ||||||
19 | Roadblock | F | (303) 555-5789 | (720) 777-4894 | ||||||
20 | Scarlett | D | (303) 555-2866 | (720) 777-2861 | ||||||
21 | Shipwreck | F | (303) 555-8798 | (720) 777-6285 | ||||||
22 | Snake-Eyes | A | (303) 555-6584 | (720) 777-2739 |