Sum guests

[Rodokanator]

Ok I am basically a newbie in this. I own a small hotel and what I need is placing the check in date of a guest and his check out date along with some details about him and be able to extract a list of guests about any day of the month. Any help?
Thanks in advance

[loginjmor]

Hi -

I would make each row of your spreadsheet data about your guest. So A1 could be last name, B1 first name, C1, check in Date, D1, check out date, and so forth. It will also be VERY helpful if you assign a unique transaction number for each stay. It can be as simple as 1, 2, 3, and so on for each stay. Even if the same guest comes back (say they had Stay number 34 and they come back a week later), give them a new transaction number such as 73 if that transaction is the 73 "guest". This will make it VERY easy to query your spreadsheet later on as you desire, AND you can sort and filter your data on date, last name, first name, etc. You can also count the number of times a particular guest has stayed with you. You can add a comment field and put random text in there about maybe a particular guests preferences, etc.

Do NOT put all the data in one column, for example A1 is Last Name, A2 first name A3 Check in, etc. That makes it VERY hard to query the data.

Finally, make a current backup of your spreadsheet. We back up our files EVERY DAY to the cloud so if the worst case happens, you're only out a days worth of data.

I bet if you hunt around, you can find some pre-made hotel guest spreadsheets to use as a template.

Hope this helps.

[PaulM100]

Here is an example based on what loginjmor said plus some formulas in F and D and A pivot table for analyse.

[Rodokanator]

Wow guys thank you very much both your answers were extremely helpful!!!
The example was amazing PaulM100 I think I can take it on from here.
Just one last thing. Is there any way I can export the list to a new sheet, depending on the date I choose?

[PaulM100]

Can you be more specific? Like in a another sheet to select a date and return a list of guest, but if you select another date, then another list and so forth??

[Rodokanator]

Yes exactly that! Being able to choose a date with a drop list and exporting the list of guests in a new sheet

[JeteMc]

Based on Paul's file (post #3) would moving the pivot table to another sheet and using the pivot filter as your drop down be an option?
Let us know if you have any questions.

[kvsrinivasamurthy]

Formula in Sheet2 in A4 te copied across.

=IFERROR(INDEX(Sheet1!A$2:A$7,AGGREGATE(15,6,ROW(Sheet1!A$2:A$7)/((Sheet1!$C$2:$C$7<=$B$2)*(Sheet1!$D$2:$D$7> =$B$2)),ROWS(A$4:A4))-ROW($A$2)+1),"")