Forum Discussion

Jasonic86's avatar
Jasonic86
Copper Contributor
Jun 15, 2023

Multi sheet look ups

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.

 

Training spreadsheet 

excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 18, 2023

    Jasonic86 

    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:

    1. Ensure that your "Roles Assigned" sheet and "Safeguarding Requirements" sheet are set up correctly with the relevant data. The "Roles Assigned" sheet should have a column with the person's name and another column with their assigned roles. The "Safeguarding Requirements" sheet should contain the roles in one column and the corresponding required training in another column.
    2. On the "Overview" sheet, you can create a table with the person's name in one column and the required training in the subsequent columns. Make sure the person's name is unique in each row.
    3. In the first cell where you want to display the required training for a person, you can use the VLOOKUP function. The formula will look something like this:

    =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.

    1. Once you have the assigned roles, you can nest another lookup function inside the previous formula to retrieve the required training from the "Safeguarding Requirements" sheet. Here's an example formula:

    =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.

    1. Copy the formula to fill down for all the other rows in the "Overview" sheet.

    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.

Resources