Hello,
I'm building this program for creating monthly rosters for a hospital department.
What it basically does is for each type of shift, it creates a table that contains only names of workers who are available for
this shift on the particular day (based on requests,quotas and previous assignments). This data is then displayed in drop down lists where the user can assign the workers.
These tables naturally have many blank cell, and it makes the drop down lists rather hard to use. So I found this formula that allows me to put
all of the names in consecutive cells (I took the formula from this video https://www.youtube.com/watch?v=cMchVJe7TDs&t=449s and changed it to work for rows instand of
columns). Now there are over 20 of these tables (each one has 31 rows for days and around 160 columns for the worker's names) and when I use this formula I'm
actually doubling the whole thing. It turns out that by doing so I am severely slowing down the program (I know that the problem is this specific formula because I've built this
program before without removing the blanks and it worked smoothly).
I've built this simple version of the program because I thought it would be too clumsy to explain in words how it works exactly. (it has 10 days insteadof 31, 6 types of shifts instead 25, 6 workers instead of 160. Additionally, I've made only one functioning type of shift table and it is the only type of shift you can assign in the main table, but it should explain the concept).
My question is how can I get the same function more efficiently so it won't be so slow? (The attached program works smoothly of course because it is on a very small scale)
Is there a more efficient formula for removing blank space? or maybe there is a better approach for the whole thing all altogether? (I should mention that the "main table" should
remain more or less the same, preferably without combo boxes of any sort)
Thanks in advance and if the way the program works isn't clear enough please let me know.
Bookmarks