I have a workbook in which the first worksheet is a signup list where column B is populated with names as people are signed up. They are then manually assigned to a table which the corresponding table number is added to column A. I want a cell in a second worksheet to look through the values of first worksheet, column A and when it finds a the number, returned the text of first worksheet, column B into the cell of second worksheet.
In the attached workbook, the Leader worksheet contains the people signed up who are Mary (B2), Joy (B3) and Leslie (B4). I manually assigned their table numbers in column A.
On the student worksheet, the red shaded boxes are placeholders for the assigned leader's name. So for Student!M3 (which is table 4), for example, I would like it to search Leader!A2:A4 for the number 4 and then return the value in column B of that row. In this case it should return the name Mary.
I am missing something in my VLOOKUP formula and am just frustrated now so I truly appreciate the help!!!
In the attached workbook, the Leader worksheet contains the people signed up who are Mary (B2), Joy (B3) and Leslie (B4). I manually assigned their table numbers in column A.
On the student worksheet, the red shaded boxes are placeholders for the assigned leader's name. So for Student!M3 (which is table 4), for example, I would like it to search Leader!A2:A4 for the number 4 and then return the value in column B of that row. In this case it should return the name Mary.
I am missing something in my VLOOKUP formula and am just frustrated now so I truly appreciate the help!!!
Sample.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Table # | Leader Name | Phone | Guests | # Reserved | # Available | # Paid | # Owes | Special Diet? | Accessible? | |||
2 | 4 | Mary | 8 | 8 | 8 | ||||||||
3 | 1 | Joy | 8 | 8 | 8 | ||||||||
4 | 5 | Leslie | 8 | 8 | 8 | ||||||||
5 | Right click here and select insert row above to add new leader | ||||||||||||
6 | |||||||||||||
7 | |||||||||||||
8 | Total Leaders: | 4 | 24 | 0 | 24 | 0 | 24 | ||||||
Leader |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2,E4 | E2 | =8 |
G2:G4 | G2 | =E2-F2 |
I2:I4 | I2 | =E2-H2 |
C8 | C8 | =COUNTIF(B2:B5, "*") |
E8 | E8 | =SUMIF($E$2:$E$5,"<>") |
F8,H8 | F8 | =SUM(F2:F5) |
G8 | G8 | =SUMIF($G$2:$G$5,"<>") |
I8 | I8 | =SUMIF($I$2:$I$5,"<>") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
J2:K5 | Cell | does not contain a blank value | text | NO |
I2:I5 | Cell Value | contains "0" | text | YES |
G2:G4 | Cell Value | =0 | text | YES |
E2:E5 | Cell Value | >8 | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
G2:G5 | Any value | |
I2:I5 | Any value | |
E2:E5 | Any value |
Sample.xlsx | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
1 | |||||||||||||||||||||||
2 | Guest | Table | Seat | Guest | Table | Seat | Guest | Table | Seat | Guest | Table | Seat | Guest | Table | Seat | ||||||||
3 | |||||||||||||||||||||||
4 | # | 1 | # | 1 | # | 1 | # | 1 | # | 1 | |||||||||||||
5 | # | 2 | # | 2 | # | 2 | # | 2 | # | 2 | |||||||||||||
6 | # | 3 | # | 3 | # | 3 | # | 3 | # | 3 | |||||||||||||
7 | # | 4 | # | 4 | # | 4 | # | 4 | # | 4 | |||||||||||||
8 | # | 5 | # | 5 | # | 5 | # | 5 | # | 5 | |||||||||||||
9 | # | 6 | # | 6 | # | 6 | # | 6 | # | 6 | |||||||||||||
10 | # | 7 | # | 7 | # | 7 | # | 7 | # | 7 | |||||||||||||
11 | # | 8 | # | 8 | # | 8 | # | 8 | # | 8 | |||||||||||||
12 | |||||||||||||||||||||||
Student |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
A4:A12 | List | =NamesUse |
E4:E12 | List | =NamesUse |
I4:I12 | List | =NamesUse |
M4:M12 | List | =NamesUse |
Q4:Q12 | List | =NamesUse |