I have a situation where room reservations can be managed by checkboxes. You can see an example in the screenshot below. I've consolidated several sheets into one to provide visualization but the data lives in separate sheets in the actual document...
1. Columns A-L are a "Manage Reservations" sheet. 2. Columns N-O are a "Pricing" sheet. 3. Columns Q-R would be the output on a "Reservation Calendar" sheet.
I have two questions:
1. Is there a better way to calculate revenue in column L?
The method I'm using is working but it's a beast to manage, doesn't feel efficient and I'm sure there's some swankier way to do a lookup and
SUM of the checkboxes / room number -> room rate.
2. How can I output the data in columns B-K (that represents the reservations) into what is shown in column Q-R?
I can't seem to figure out a way to QUERY or PIVOT the checkboxes and dates they represent into individual rows in another sheet. I had first tried some PIVOT tables but was running into issues with, what I gather is the source data formatting.
I'd like to keep the formatting of columns
A-L, if possible. It's easy to manage and visualize. That said, if it needs to change to optimize the calculations and allow for me to get the output of columns
O-P on another sheet... I'll take that as a lesson in better spreadsheet architecting.
Any help would be appreciated!