Formula help for multiple lookups over two sheets

[CLD32]

Hi all,

I am new to the forum and not very experienced with formulas in excel.

I've created a workload planner which has multiple rows for each person based on the aspect of work they will be completing each day. Basically I now want to have a simplified table on a second sheet that will auto populate based on any updates to the detailed planner, that just shows one line for each person and whether they have work scheduled on each day.

So basically, can I use a formula for looking up a persons name on multiple rows on one sheet and combining this on to one row on another sheet within the same workbook? I have the people set up in a dynamic table so can use the table nomenclature if this will work.

I'm not sure how to add a screenshot but that would probably explain it better.

Thanks!

 

[istiasztalos]

Hi,

Not sure I understand what exactly you are looking for. Do you just want to check based on a distinct list of people whether they appear at least one time with a given data in another sheet/table?

For that a COUNTIFS would do if you want to use formulas (though the easiest would be to use a Pivot table instead)

Assuming your user names are in column A, task dates are in B, you can have a summary table like this:
[TABLE="class: grid, width: 620"]
<tbody>[TR]
[TD][/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]=TODAY()[/TD]
[TD]=TODAY()+1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jake[/TD]
[TD]=COUNTIFS($A$2:$A$9,$E2,$B$2:$B$9,F$1)[/TD]
[TD]=COUNTIFS($A$2:$A$9,$E2,$B$2:$B$9,G$1)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Julie[/TD]
[TD]=COUNTIFS($A$2:$A$9,$E3,$B$2:$B$9,F$1)[/TD]
[TD]=COUNTIFS($A$2:$A$9,$E3,$B$2:$B$9,G$1)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Adam[/TD]
[TD]=COUNTIFS($A$2:$A$9,$E4,$B$2:$B$9,F$1)[/TD]
[TD]=COUNTIFS($A$2:$A$9,$E4,$B$2:$B$9,G$1)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sophie[/TD]
[TD]=COUNTIFS($A$2:$A$9,$E5,$B$2:$B$9,F$1)[/TD]
[TD]=COUNTIFS($A$2:$A$9,$E5,$B$2:$B$9,G$1)[/TD]
[/TR]
</tbody>[/TABLE]