Finding Employees on a Different Sheet

[Robby19]

Good day,

I have a full list of employees on one sheet (master sheet). And a few other sheets (sheet 1, 2, 3, etc...) that will be filled in with the employees name.

What I need is to have a formula on the master sheet that looks at Column C, Row 4 and down on each sheet to compare the same employee number. If it can find the number, then the result of the formula should be "Y" or "Yes." If it cannot find the same number on any of the other sheets, then the result should be "N" or "No."

I cannot change the layout of the other sheets or I would just combine them all together and use an Index(Match)) statement.

See below for a visual;

Master Sheet
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Number
[/TD]
[TD]Attending?
[/TD]
[/TR]
[TR]
[TD]123456789
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]987654321
[/TD]
[TD]Yes
[/TD]
[/TR]
</TBODY>[/TABLE]

The result on the Master sheet for Attending 987654321 is Yes since the same number appears on Sheet 1. The result for 123456789 is No because it does not appear on either sheet 1 or 2.

Sheet 1
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Number
[/TD]
[TD]Table
[/TD]
[/TR]
[TR]
[TD]123456888
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]987654321
[/TD]
[TD]2
[/TD]
[/TR]
</TBODY>[/TABLE]


Sheet 2
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Number
[/TD]
[TD]Table
[/TD]
[/TR]
[TR]
[TD]112345678
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]111345678
[/TD]
[TD]4
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Yongle]

How about

=IF(COUNTIF(Sheet1!C:C,Master!A2)+COUNTIF(Sheet2!C:C,Master!A2)+COUNTIF(Sheet3!C:C,Master!A2)+COUNTIF(Sheet4!C:C,Master!A2)+COUNTIF(Sheet5!C:C,Master!A2)>0,"Yes","No")

extend as required

 

[Robby19]

You are the best. In all honesty though I should have thought to do this. Haha. Thanks!

 

[Yongle]

I should have thought to do this

None of us is immune from that one. Thanks for the feedback

 

[Robby19]

Now to take this one step further. What if I wanted to pull the Table # they are assigned to from the different sheets.

This is what the Count formula looks like.

=IF(COUNTIF(Table1t24[EDIPI],[@EDIPI])+COUNTIF(Table25t50[EDIPI],[@EDIPI])+COUNTIF(Table51t76[EDIPI],[@EDIPI])+COUNTIF(Table77t107[EDIPI],[@EDIPI])+COUNTIF(Table108t115[EDIPI],[@EDIPI])+COUNTIF(TableHead[EDIPI],[@EDIPI])>0,"Yes","No")

I think SUMPRODUCT would be the solution here.

For example;
Master Sheet (Using above example from Post #1 )
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Number
[/TD]
[TD]Attending
[/TD]
[TD]Table
[/TD]
[/TR]
[TR]
[TD]987654321
[/TD]
[TD]Yes
[/TD]
[TD]2
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Yongle]

How about..

=IF(COUNTIF(Table1t24[EDIPI],A2)>0,"1t24",IF(COUNTIF(Table25t50[EDIPI],A2)>0,"25t50",IF(COUNTIF(Table51t76[EDIPI],A2)>0,"51t76",IF(COUNTIF(Table77t107[EDIPI],A2)>0,"77t107",IF(COUNTIF(Table108t115[EDIPI],A2)>0,"108t115",IF(COUNTIF(TableHead[EDIPI],A2)>0,"Head",""))))))

replace A2 with the reference to value that is being found

 

[Robby19]

Yes and No. The 1t24 stands for tables 1 - 24. Within the tables table it looks like this;

Table1t24 (All the way to Table # 24)
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Number
[/TD]
[TD]Table #
[/TD]
[TD]Seat #
[/TD]
[/TR]
[TR]
[TD]987654321
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]123456789
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]111111111
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[/TR]
</TBODY>[/TABLE]

So I need it to look within each Excel Table, and find the number and the Table # (within the excel table), yes this is getting confusing with all of the tables lol, and show me that number.

For this example, instead of 987654321 showing 1t24, it should show;
Master Sheet
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Number
[/TD]
[TD]Attending
[/TD]
[TD]Table #
[/TD]
[/TR]
[TR]
[TD]987654321
[/TD]
[TD]Yes
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]123456789
[/TD]
[TD]Yes
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]111111111
[/TD]
[TD]Yes
[/TD]
[TD]2
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Yongle]

I am sure it makes senses to you, but it is a bit foggy at this end

My formula returned the Excel Table name but that is not what you want

Do you want to return a value contained in the table (which happens to be Table# - presumably a table you sit at ) ?
- if so, then need to use MATCH function

Let me know

your formula is going to get a bit ugly

 

[Robby19]

My formula returned the Excel Table name but that is not what you want

Do you want to return a value contained in the table (which happens to be Table# - presumably a table you sit at ) ?
- if so, then need to use MATCH function

Let me know

This is correct. So in each execl table (Table1t24, Table25t50, etc) there is a column named Table #. Additionally, on the Master sheet, there is a column named Table #. I would like the formula to look for the same Number(EDIPI, from above) in each of the tables (Table1t24, Table25t50, etc) and if a match is found, then display the Table # value from that table.

 

[Yongle]

As predicted, rather ugly, but it works

=IFNA(INDEX(Table1t24[Table '#],MATCH([@EDIPI],Table1t24[EDIPI],)),IFNA(INDEX(Table25t50[Table '#],MATCH([@EDIPI],Table25t50[EDIPI],0)),IFNA(INDEX(Table51t76[Table '#],MATCH([@EDIPI],Table51t76[EDIPI],0)),IFNA(INDEX(Table77t107[Table '#],MATCH([@EDIPI],Table77t107[EDIPI],0)),IFNA(INDEX(Table108t115[Table '#],MATCH([@EDIPI],Table108t115[EDIPI],0)),IFNA(INDEX(TableHead[Table '#],MATCH([@EDIPI],TableHead[EDIPI],0)),"NOT FOUND"))))))


If function IFNA is not available in your version of Excel replace with IFERROR