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]

 

[Robby19]

Works perfectly for my needs. Thank you again!

 

[Yongle]

Or using VLOOKUP
- in this formula, Table # is in column 4 in each table and EDIPI is in column 1

formula looks a bit more digestible
=IFNA(VLOOKUP([@EDIPI],Table1t24,4,0),IFNA(VLOOKUP([@EDIPI],Table25t50,4,0),IFNA(VLOOKUP([@EDIPI],Table51t76,4,0),IFNA(VLOOKUP([@EDIPI],Table77t107,4,0),IFNA(VLOOKUP([@EDIPI],Table108t115,4,0),IFNA(VLOOKUP([@EDIPI],TableHead,4,0),"NOT FOUND"))))))

 

[Robby19]

Unfortunately the EDIPI is in the 4th column and I am not allowed to move it, so VLOOKUP does not work.

Here is another fun one for you. How can I show how many guests each person is bringing on the master sheet. For example;

Master Sheet
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Paid?[/TD]
[TD]Table #[/TD]
[TD]Seat #[/TD]
[TD]# of Guests[/TD]
[TD]EDIPI[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]123456789[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]987654321[/TD]
[/TR]
</TBODY>[/TABLE]

Table1t24
[TABLE="width: 500"]
<TBODY>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]EDIPI[/TD]
[TD]Guest[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]a[/TD]
[TD]132456789[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]c[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]d[/TD]
[TD]987654321[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

The guest will always be directly under the "sponsor."

 

[Yongle]

Now it is your turn...

Something worth a try is Search and Replace on the formula

Replace
Table #
with
# of Guests

 

[Robby19]

The easy answer is to use the same EDIPI for each guest that matches the sponsor and do a count of that, however that will make the Table1t24 tab look unpleasing to the eyes. Other than that I am at a loss. Maybe an Count of Indirect(Search) or some sort? Lol

 

[Yongle]

Is it either 1 guest or none (based on Yes in that column) OR is there an additional column holding number of guests?


PS - it would be much easier if all the feeder tables were combined into ONE table with an extra column added containing ExcelTableRef