Jun 15 2023 05:25 AM
Jun 15 2023 05:25 AM
I am trying to build a training log spreadsheet, I have people with multiple roles and I want the first sheet 'Overview' to black out the training that each person is not required to complete.
What I'm hoping is that I can build a function that looks up a person on the 'Roles Assigned' sheet and then further lookup the required training for their assigned roles from the 'safeguarding Requirements' sheet and populate accordingly on the overview sheet.
I am having an absolute mare trying to suss out how to do it!
If anyone can help I would be incredibly grateful.
Jun 17 2023 10:34 PM
To achieve the desired functionality in your training log spreadsheet, where the "Overview" sheet populates the required training for each person based on their assigned roles, you can use a combination of lookup functions like VLOOKUP or INDEX/MATCH.
Here is a step-by-step approach to help you set it up:
=VLOOKUP(A2, 'Roles Assigned'!A:B, 2, FALSE)
In this formula, A2 is the cell containing the person's name in the "Overview" sheet. 'Roles Assigned'!A:B refers to the range of cells in the "Roles Assigned" sheet containing the person's name and assigned roles. 2 specifies that the function should return the second column from the range, which corresponds to the assigned roles. The FALSE argument indicates an exact match is required.
=VLOOKUP(VLOOKUP(A2, 'Roles Assigned'!A:B, 2, FALSE), 'Safeguarding Requirements'!A:B, 2, FALSE)
This formula performs the first VLOOKUP to retrieve the assigned roles for the person, then performs another VLOOKUP to find the corresponding required training in the "Safeguarding Requirements" sheet. Adjust the ranges 'Roles Assigned'!A:B and 'Safeguarding Requirements'!A:B based on the actual range in your sheets.
By using these nested lookup functions, you should be able to populate the required training for each person based on their assigned roles. Adjust the formulas and ranges as needed to match your specific spreadsheet structure.
Note: File was not opened for personal security reasons. I do not create finished files, do not take on commissioned work, only give help for self-help...as far as I can. Hope this helps you.