Jack has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course
Word Advanced course
Taking Minutes course
Office 365 End User course
Introduction to Management course
Query - automatic population of data
Hi All - Question:
I have a sheet with multiple tabs that is tracking the allocation of certain tasks (with a specific reference number) to particular members of a participant list. The sheet comprises:
- Tab 1: a list of confirmed participants, with columns for the references of tasks assigned to each one and a count of the tasks assigned.
-Tab 2: a list of tasks with specific reference numbers, with three columns for the three different participants they have been assigned to.
My question is - is there a way/function I can use whereby when I write the participant in one of the columns of tab 2, the reference number of the assigned task automatically appears against the participants name in the associated column of tab 1?
RE: Query - automatic population of data
Hi Jack
Thank you for using the forum to ask a question.
Yes, you can achieve this using a combination of Excel formulas—specifically INDEX, MATCH, and FILTER
Assuming the following is true
Tab 1 is named Participants
Tab 2 is named Tasks
In Tasks, each row has a task reference in column A, and participant names in columns B, C, and D.
In Participants, column A lists participant names, column B lists assigned task references (ideally as a concatenated list), and column C counts the number of tasks.
Use FILTER to pull task references
In Participants!B2 (next to the first participant name), use:
=TEXTJOIN(", ", TRUE, FILTER(Tasks!$A$2:$A$100, (Tasks!$B$2:$D$100=Participants!A2)))
This will list all task reference numbers from Tasks where the participant in Participants!A2 appears in any of the assignment columns.
=COUNTIF(Tasks!$B$2:$D$100, Participants!A2)
This counts how many times the participant appears across the three assignment columns.
Try that and see if that gets you closer to the result. Change the references/names as I've made assumptions.
Kind regards
Richard
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Query - automatic population of data
Hi Richard
Thanks for this! However, I must admit that I simplified the query somewhat to see if there was a solution, and cant seem to translate the proposed formula to my actual spreadsheet.
Is there any way I can organise a meeting with yourself or another trainer to discuss?
RE: Query - automatic population of data
Hi Jack
Thanks for clarifying and we understand it can be hard to translate sometimes.
The post course learning support forum has been designed to help with points covered in the courses. This helps you embed the learning. We don't offer remote support help on a call/email as part of that is often a move into consultancy as we frequently need to see your data to help. You'd need to discuss with your learning solutions partner at STL who is Jacob, I'll let him know
Possible solutions could look like another level e.g. Excel Advanced Formulas and Functions on a public course, a Bespoke session if there are others who need similar areas in Excel like Power Query (which would be organised between HR and STL), or the option of a coaching/consultancy session to help.
Kind regards
Richard
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
