Chandler8
New Member
- Joined
- Jun 29, 2022
- Messages
- 23
- Office Version
- 365
- 2016
- Platform
- Windows
Hey guys if I could get some help with a formula that would be great! So basically what I need is "Our policy is 2 different stays maximum in a lifetime" So I have a check_in 1, check_out 1, check_in 2, check_out 2 box in excel. What I need is a formula that basically grabs anytime that their name is mentioned on the guest_Info sheet; it pulls the check in and check out days per person to be listed on the guest list. If that makes sense. Here is some mini sheets to take a look. I assume its going to be a v-lookup formula but not totally sure. And you may not understand what I'm saying, but here it is!
You can ignore the B column in Guest_List. Its C4,D4,E4, and F4. Filled all the way down. This will tell me if the person has had their 2 maximum lifetime stays! Once again the Guest_Info list starts at F4:V1000 for future entrys! Thanks for taking a look.
| Rental Arrivals.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 3 | Check_In | Check_Out | #_of_Days | Property | Lease_Holder | Registered_Guest1 | Registered_Guest2 | Registered_Guest3 | Registered_Guest4 | Registered_Guest5 | ||
| 4 | 5/19/2022 | 5/21/2022 | 2 | R Hart: 152 Island Drive, Unit B | Catherine Frye | Catherine Frye | Rick Jones | |||||
| 5 | 5/21/2022 | 5/22/2022 | 1 | Leary: 170 Loch Dornie, Unit E | Greg Proctor | Greg Proctor | Trina Proctor | Mark Poovey | Sharon Poovey | |||
| 6 | 5/26/2022 | 5/30/2022 | 4 | Laskin: 1236 Mountain Springs | Neal Blinde | Neal Blinde | Jessica Blinde | Arden Blinde | Norah Blinde | Graham Blinde | ||
| 7 | 5/26/2022 | 5/31/2022 | 5 | L. Hart: 934 Hillside Drive | Stacie Menichella | Stacie Menichella | Dan Menichella | Bryce Menichella | Brandon Artiqua | Lauren Artiqua | ||
| 8 | 5/27/2022 | 5/30/2022 | 3 | Belk: 987 Mountain Springs | Dave Powell | Dave Powell | Meredith Powell | Porter Powell | Rivers Powell | Ella Powell | ||
| 9 | 5/27/2022 | 5/30/2022 | 3 | Young: 282 North Wildflower | David Taylor | David Taylor | Elizabeth Taylor | Broughton Taylor | William Taylor | |||
| 10 | 6/17/2022 | 6/19/2022 | 2 | Sewell: 210 Loch Dornie, Unit B | Michael Patton | Michael Patton | Amy Patton | |||||
| 11 | 6/18/2022 | 7/1/2022 | 13 | Laskin: 1236 Mountain Springs | Anne Warren | Anne Warren | Ellie Waren | Timothy Warren | Claire Warren | Jeff Warren | ||
| 12 | 6/19/2022 | 6/22/2022 | 3 | Porterfield: 161 Mountain View | Patrick Walsh | Patrick Walsh | Emily Walsh | Michael Walsh | Martha Walsh | Patrick Jr. Walsh | ||
| 13 | 6/22/2022 | 6/26/2022 | 4 | Leary: 170 Loch Dornie, Unit E | Mark Frisch | Mark Frisch | ||||||
| 14 | 6/23/2022 | 6/26/2022 | 3 | Porterfield: 161 Mountain View | Anne McGowan | Cam Findlay | Amy Findlay | David Findlay | Susan Findlay | Kathryn Beiser | ||
| 15 | 6/23/2022 | 6/30/2022 | 7 | L Hart: 934 Hillside Drive | Hans Stig Moller | Hans Stig Moller | Nancy Stig Moller | Catherine Stig Moller | Rita Stig Moller | Jens Stig Moller | ||
Guest_Info | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C5:C15 | C5 | =(B5-A5) |
| Rental Arrivals.xlsm | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 3 | Guest_Name | Guest_Last_Name | Check_In_1 | Check_Out_1 | Check_In_2 | Check_Out_2 | Sponsor Name | Lease_Holder | ||
| 4 | #VALUE! | |||||||||
| 5 | #VALUE! | |||||||||
| 6 | #VALUE! | |||||||||
| 7 | #VALUE! | |||||||||
| 8 | #VALUE! | |||||||||
| 9 | #VALUE! | |||||||||
| 10 | #VALUE! | |||||||||
| 11 | #VALUE! | |||||||||
| 12 | #VALUE! | |||||||||
| 13 | #VALUE! | |||||||||
| 14 | #VALUE! | |||||||||
| 15 | #VALUE! | |||||||||
Guest_List | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4:B15 | B4 | =RIGHT([@[Guest_Name]],LEN([@[Guest_Name]])-FIND(" ",[@[Guest_Name]])) |
You can ignore the B column in Guest_List. Its C4,D4,E4, and F4. Filled all the way down. This will tell me if the person has had their 2 maximum lifetime stays! Once again the Guest_Info list starts at F4:V1000 for future entrys! Thanks for taking a look.
