Looking for ideas on how to make certain cells interact within another and be able to reference it as well
Our take
I hope this doesnt get taken down, as I am not super knowledgable within excel but learning as i go. This is niche IMO and have tried looking up different solutions in many different ways and I got about half of this project done. I am a restaurant manager and we are looking to redo our reservation layout system. This is hyper specific to when we are open on holidays as we are reservation only and just about double our volume. In the past, we have done everything by paper and that has proved itself to be ineffective. I have created a sheet where 15 minute increments are on the y-axis and table numbers are the x-axis with the minimum and maximum for that party can be sat at. parties of 1-4 have time increments of 1.5 hours, 5-6 are 1 hour and 45 minutes, and 7+ is 2 hours. Currently I have if you input the party size into a time block, it will automatically block out the time slot allotted for that party size. The idea is to get as many rotations as possible for tables without too many guests coming into the building at once. I will attach screenshots of what i currently have. The issue Im having now, is in order to take the reservations, we will needs a guests, name, phone number, email, and credit card while also being able to reference where and what time their reservation is at. Im thinking a second sheet that interacts with the first.
The formula i used to complete the time blocking =OR(AND(OFFSET($ROW$#,-1,0)>=(MINIMUM PARTY SIZE),OFFSET($ROW$#,-1,0)<=(MAXIMUM PARTY SIZE))
in the screenshots, you can see, if you enter a party size that fits within that table, it will turn green and then the cells beneath will turn red in accordance to how long the party size is allowed. I am not completely finished as there are few formulas i need to tweak but I am aware on how to do so. Then the part that is separated by party size, I will have that follow the same formulas, this is for my colleagues, so it is easier for them to follow. As you can see this only gives us information on how many guests could be sat and what time, but there needs to be an area that takes down guests information with everything previously stated included, and being able to easily reference what table they would be sat at and what time.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Data Automation - Pulling data from a sheet but also blocking cellsMy main purpose for this is to be able to create a trackable booking spreadsheet, the main input page is where the kids are booked onto sessions this is then checked against when they enrol and leave. I have Data in my 'Input' sheet this is split into 5 sessions per day 5 days. Column A is the name Column B:F is Monday 5 sessions Column G:K is Tuesday 5 sessions and so on I then have a sheet 'Apr 26' which then pulls this data into the monthly sheet The data i have in my input sheet can have anywhere between 1 cell to 12 cells per session, my formula works if there is 2 cells or more per session but doesnt work if only 1 cell it puts that cells data over multiple cell. Excel Formula Code (PasteBin) Excel Doc Link - ignore sheets past May 26 as they dont have the updated code or conditional formatting in. Excel Doc Link (Non Macro) This is my main input data sheet This is the sheet and cell (highlighted) that the formula relates to As you can see in the above images on Monday on the ASC 5pm column it multiples the data (child 1) If I have 2 lots of data in the ASC 5pm Monday column on the input sheet the formula works properly. Input sheet is how sessions per child are booked. sessions can only have 12 in however on certain days due to staffing can only have a certain number in (say 9) the formula im having trouble with is on sheet Apr 26 on column F it repeats the 'child 1' cell multiple times rather than just doing It once but as you can see on column K the formula works but just not when its got 1 cell of data Version Excel 2024 on Mac but needs to work on windows as well submitted by /u/BarrowBluebird [link] [comments]
- Creating a dynamic reactive sheetI'm sure I may be asking for the moon - but it's worth a shot. I work in a travel company and they require us to manually type out a quote sheet (format is barely there). I wanted to make two tables that would speed things up. The first table would have a table that has the following columns Day & Date / Category / Arrangement / Unit Price / Quantity / Total / Remarks & Comments I had C5 and E5 hold start and end dates and then had a macro basically populate the table according to the number of days in the expected tour. Problem I found was that if I ever wanted to go back and edit or change those it would be borderline impossible. The next table would basically read the category column and if it found for example (Category = Accommodation) it would extrapolate the data and populate it. Repeat this for all subsequent categories. My current method is using =IFERROR( IF( FILTER(Quote[Arrangement], Quote[Category]='Information Reference'!A7)=0, "", FILTER(Quote[Arrangement], Quote[Category]='Information Reference'!A7) ), "" ) Any guidance would be great. I tried using ChatGPT to help guide me through it and started learning about Macros. I kept running into a wall that it would sometimes update and then nuke the original table - without that table all references are lost and the entire thing implodes. submitted by /u/BenToJapan [link] [comments]
- Trouble coordinating auxiliary sheet in an online workbook to sheet w/ primary dataI have an online workbook that is used to record, monitor, and manage a full year's academic schedule for the college I work for. I have recently become the one in charge of this workbook, and I have spent many hours improving it and making it both more automated and more foolproof. This workbook has several sheets that, at times, reference each other. One sheet is basically the primary data set that shows the actual schedule with 20+ columns of details per row. Another sheet is there for the purposes of tracking and managing non-course-related releases and work that would reduce faculty workload. As such, this data is also listed on the primary sheet ("Master Schedule"). The problem I am having is this; on the auxiliary sheet that is used for tracking non-course-related work of the faculty ("Release Tracking"), all the columns in this table are auto generated based on the data on the Master Schedule, except for column I. Column I is where I manually select (from a data-validated list) a status (pending, accepted, denied, etc.) to assign to the release. The reason I want this data on Release Tracking instead of Master Schedule is because that the number of instances when the condition that triggers something to populate on Release Tracking is only about 3-5% of the total data on Master Schedule. Plus, Master Schedule is already super wide, so avoiding adding more columns to that table is highly preferable. If you haven't guessed already, my trouble is that when the Master Schedule is resorted or the row order of that table is otherwise changed, the status in I of Release Tracking do not move with the rows on that table. I did forget to mention that both Master Schedule and Release Tracking are both formatted as proper tables. I have a hidden helper column in the Master Schedule table that, when a particular condition is met, triggers a unique ID that another hidden helper column in the Release Tracking table can use to populate that table. That formula is: =IF(C12="N/A","MS_"&ROW(),"") The formula in the hidden helper column of Release Tracking is: =IFERROR(INDEX(master_schedule_table[Index ID],AGGREGATE(15,6,(ROW(master_schedule_table[ [ CRN] ])-ROW(INDEX(master_schedule_table[ [ CRN] ],1,1)) + 1)/(master_schedule_table[ [ CRN] ]="N/A"),ROWS($J$2:J2))),"") The formula in the A column of Release Tracking is: =IF(J2="","",IFERROR(INDEX(master_schedule_table, MATCH(J2, master_schedule_table[Index ID], 0), 12), "")) And the rest of the columns (B:H) follow this pattern except that they reference the appropriate correlative column on the Master Schedule. I have spent probably 15-20 hours trying to figure out a solution, but everything I try that allows me to keep the functionality and design of the workbook the way I want it, fails because, ultimately, the formulas on Release Tracking involve elements of relative positioning and because I cannot find a way to write the ID-generating formula in the helper column of the Master Schedule to create an ID with at least a static element and triggers the ID to come and go as the condition in column C of the Master Schedule comes and goes. To summarize what I want, I need a solution that 1) avoids the problem of the rows on Release Tracking changing when the Master Schedule's rows are changed or reordered (thereby linking my status selections to rows other than the one I want that status to be linked to), 2) allows me to manage/change/update the status on Release Tracking instead of Master Schedule, and 3) at least filter the rows on Release Tracking. Release Tracking doesn't necessarily have to be a full table, but for practical purposes, I need to at least be able to filter those rows. Is this possible with an online Excel workbook or am I just spinning my wheels and wasting my time trying to make this happen? submitted by /u/AndrewRyanPatrick [link] [comments]
- Want to copy data from master sheet to seperate sheetHi, I am an excel newbie and I would have added a screenshot but it is all in Dutch. I am in charge of the guestlist for a big company multiple day event in October. Right now I have everyone in one sheet with their names and contact information and also a column (M) which is the day they will be invited for the event. it's either on the 30th of september, 1st of October or the 2nd of October. I do not know if it is possible but I would like to have the whole row copies to a different sheet when I fill in the date they would be invited on. So if person A gets an invite for 30/09, I would like the full row of their information to be copies to my different sheet that is specifically for all the guests of 30/09. This way I have my own sheet with everyone that is invited from 30/09 until 02/10, and then different sheets per day. Is this possible to do this with a formula or something more automatic instead of copy pasting the lines myself? Thank you submitted by /u/Necessary_Fennel581 [link] [comments]