•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Issues with scaling a work roster schedule
Our take
Scaling a work roster schedule can be challenging, especially when relying on a leave register to automate AM/PM shifts. By counting blank cells to determine staff availability and filtering a randomized list, you may find it cumbersome to manage variations without manual input. The existing formulas for populating names in the schedule require constant adjustments, complicating the process as you aim for efficiency. Embracing a more streamlined approach could transform your scheduling workflow, allowing for greater flexibility and reduced manual effort over time.
Using a leave register I am trying to automate an AM/PM work roster.
The Leave Register counts blank cells to confirm who's available, & I'm filtering a randomised list of available staff in a separate column (N:Q in image 2):
=LET(
P1AM, FILTER('Leave Register 2026'!A92:A98, 'Leave Register 2026'!C92:C98 = ""),
SORTBY(P1AM, RANDARRAY(ROWS(P1AM)))
)
I'm using variations of the formula below in C11:C28 & D11:D28:
=LET(
P1AM, N4#,
UsedNames, VSTACK(range to check to avoid duplication),
AvailableNames, FILTER(P1AM, ISNA(XMATCH(P1AM, UsedNames)), ""),
IF(ROWS(AvailableNames)=0, "", INDEX(AvailableNames, 1))
)
The Sales cells (Rows 4 & 7) are manually entered by the user at the start of the week, so each cell below this checks these before populating a name, along with any cells populated prior to the specific cell in question. As each cell has a different range it's checking, this seems hard to grow without manual entry across a week let alone at an annual scale.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Weekly changing roster to track changes while maintaining data regarding completed assessments.Alright, I will try to explain this as clearly as i can. There is a constant movement of clients in and out of my job. I need to track whether or not an assessment has been done for each client, but the population is constantly fluctuating. There is a weekly roster that comes out to compare last week's clients to this week's. What I would like to do is take information from spreadsheet W (the weekly roster sent out), and update spreadsheet T (the one to track my assessments). The attached picture is spreadsheet T. The yellow highlight on the "Old List" indicates that the assessment is done, the "New List" was directly pasted from spreadsheet W. Column K is as far as I got with conditional formatting as the formulas is: =IF(ISERROR(MATCH(I3:I113, C3:C113, 0))=TRUE, "New", "Old") I want to the highlight to automatically transfer over to the "New List" and then going forward that would be copy/pasted to the "Old List" the following week. https://preview.redd.it/jvpvjakm37vg1.png?width=625&format=png&auto=webp&s=590c0504766533da8e55605c9fc545136c26baa0 submitted by /u/No_Spend1267 [link] [comments]
- Monthly tracking workbook I use to track employee sales metrics; Trying to find a way to make the process less labour intensiveTruly having a hard to describing my issue effectively but hoping someone can help. First time posting here and I'm by no means an expert with excel, so please be kind! I have a monthly workbook where I track each employees revenue and other metrics. Every 2 weeks for payroll, I provide a print out of these numbers, and the payroll sheet pulls data from multiple sheets in the workbook. For example, every workbook has a separate sheet for each day of the month, titled "1" through "31". I have pay period sheets, so I'll use one titled "04.02.26 - 04.15.26". Then I'll have the data for each employee pulled from multiple sheets. For example, I use the formula =SUM('2:15'!E3) to pull the sales data from each day of that period for the specific employee. This works quite well. However, when I create a new month's spreadsheet, I have to manually alter this formula for each employee and for each data point (more than just revenue, at least 6 different data points for 7 employees). Is there a way to automate this? For example, a cell or two where I'm able to enter the date range and all of the formulas update to that date range for the corresponding pages? I'm sorry of this post is confusing. Truly it's confusing even typing it! submitted by /u/Ok_Smile9222 [link] [comments]
- Generating a random selection of rows based on multiple criteriaI am trying to create a force building tool for a tabletop wargame. I would like it to generate multiple rows of unit info from a table based on certain criteria and have it working for the most part but keep ending up with #SPILL errors. I am sure that there is a much more efficient way of going about this. Here's the overview: https://preview.redd.it/4ghjhqiupksg1.png?width=1256&format=png&auto=webp&s=a6bdc0749397463685bc7f52d8e90f9199fb8d62 The breakdown: A random rom is selected from a table in the worksheet https://preview.redd.it/5telq4v4qksg1.png?width=757&format=png&auto=webp&s=f25aeec460a6b8e5c5750f9d3ff55acaef477658 Then a filter is applied to sort the results according to what is selected in the dropdown menu at B2 https://preview.redd.it/8mt7wxadqksg1.png?width=1236&format=png&auto=webp&s=1e2e6bc4cde488809447de96e1a8d38e8182e8f2 For the most part this method seems to work. The issue is that every few refreshes i get a #SPILL error https://preview.redd.it/ndkvd72nqksg1.png?width=257&format=png&auto=webp&s=57bbb4d9217c9166376c2935a0ce4f85c4b386c0 I have set this formula to run at the bottom of the sheet and ensured there was no data in any cells to the farthest right and down. BONUS: If there is also a way to get this to work and have the random rows be equal to a determined point value. that would be amazing. submitted by /u/simpson95338 [link] [comments]
- Stop using ungodly INDEX math to flatten 2D schedules. TOCOL() + FILTER() is all you need.This comes up constantly. Someone gets handed a resource tracker or a system export where tasks are split across "Morning Task" and "Afternoon Task" columns, and they need a flat list to dump into a Pivot Table. Simple enough ask. The fun part? Half these exports don't even leave cells blank - they write out [empty] as literal text, so any trick that relies on detecting blank cells just falls flat. And on top of that there's usually a Status column you need to drag along, but only once per person - not stamped next to every single task row like a broken rubber stamp. Old solution was some deeply cursed nested INDEX/ROW formula that nobody could read six months later. If you're still doing that, please stop. On Office 365 you can handle the whole thing in one shot: =LET(data, A2:C11, status, D2:D11, col_data, TOCOL(data), col_status, TOCOL(IF(SEQUENCE(1,COLUMNS(data))=1, status, "")), FILTER(HSTACK(col_data, col_status), col_data<>"[empty]")) TOCOL flattens the grid, the IF/SEQUENCE combo makes sure the status only shows up next to the name and not repeated under every task, HSTACK glues the two columns together, and FILTER kills all the [empty] noise. Keep your ranges the same size throughout or you'll get a #VALUE! staring back at you. These dynamic array functions honestly flew under the radar for a lot of people who don't spend their weekends reading Excel update logs. Hope it saves someone a headache. Note; for Excel 2019 and 2021 you can use power query. However in the 2021 version you can use the filter and sequence function. In 2021 version; the formulas are; =FILTER(INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1),INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1)<>"[empty]") and =FILTER(IF(INT((SEQUENCE(30)-1)/10)+1=1,INDEX(D2:D11,MOD(SEQUENCE(30)-1,10)+1),""),INDEX(A2:C11,MOD(SEQUENCE(30)-1,10)+1,INT((SEQUENCE(30)-1)/10)+1)<>"[empty]") one thank go at user for pointing it out to me Excel_User_1977 submitted by /u/Good-Willingness2234 [link] [comments]
Tagged with
#rows.com#financial modeling with spreadsheets#AI formula generation techniques#Excel compatibility#Excel alternatives for data analysis#formula generator#Excel alternatives#work roster#leave register#automate#available staff#blank cells#filtering#LET function#randomised list#SORTBY#RANDARRAY#INDEX function#ROWS function#ISNA