Google Sheets: How to conditionally populate a tab with specific rows from another tab-CodePudding

I have a google sheet that has information populated on the first tab completed training. The other tabs in this sheet represent different departments. I would like to pull data from each row from the completed training tab to the tab of the respective department as names are added to the document, based on whether or not the Completed? row says yes.

For example, if Jane Doe from CS has completed the training, I would like to have the document automatically populate that row in the CS tab in the document.

CodePudding user response：

You can use a filter formula to achieve this:

=FILTER('Completed Training'!A2:G, 'Completed Training'!C2:C="People", 'Completed Training'!D2:D="Yes")

Change "[DEPT NAME]" to whatever department you want to filter. I have tested this in a copy of your sheet and it works for me. Please let me know if you have any questions

CodePudding user response：

1 → To populate on exactly the same line as the original tab!

People tab in A2:

=ARRAYFORMULA(IF(('Completed Training'!C2:C="People")*('Completed Training'!D2:D="Yes"),'Completed Training'!A2:G,""))

Sales tab in A2:

=ARRAYFORMULA(IF(('Completed Training'!C2:C="Sales")*('Completed Training'!D2:D="Yes"),'Completed Training'!A2:G,""))

CS tab in A2:

=ARRAYFORMULA(IF(('Completed Training'!C2:C="CS")*('Completed Training'!D2:D="Yes"),'Completed Training'!A2:G,""))

If you were going to use it in only one line, you could use AND(condition_1,condition_2) for multiple conditions, but in ARRAYFORMULA() you can't use AND(), so to get around this we use (condition_1)*(condition_2), which works exactly the same way.

2 → To always populate the first rows without blank rows between the values!

People tab in A2:

=IFERROR(FILTER('Completed Training'!A2:G,'Completed Training'!C2:C="People",'Completed Training'!D2:D="Yes"))

Sales tab in A2:

=IFERROR(FILTER('Completed Training'!A2:G,'Completed Training'!C2:C="Sales",'Completed Training'!D2:D="Yes"))

CS tab in A2:

=IFERROR(FILTER('Completed Training'!A2:G,'Completed Training'!C2:C="CS",'Completed Training'!D2:D="Yes"))