dallekvist
New Member
- Joined
- Feb 2, 2021
- Messages
- 2
- Office Version
- 2016
- Platform
- Windows
- MacOS
Hi guys and gals!
I've been using this forum a lot to get inspiration to build a spreadsheet for work that is both functional and simple to use. I realized that "simple" features in Excel often have complex functions and/or codes (VBA) that you have to master before enjoying your hard work.
I'll try to explain to the best of my abilities with a little background information, so here goes.
I've built this caseworker assignment sheet (attached) that randomly selects a caseworker to cases by using one of the macros "Assign CW1, Assign CW1 Uneven Weeks, Assign CW2, Assign CW2 Uneven Weeks." You'll find the buttons in the "Assign" sheet. (Column M2)
Here is where it gets a little tricky.
Some of these cases are "bundles," (See column H) which means they should be processed by the same caseworker (CW1 and CW2) for easy processing. I have assigned a number to those who need to be processed together in column H.
Based on the numbers listed in that column, I want to choose a random CW1 and random CW 2 - just like I did the first time when I assigned them - to process those cases; HOWEVER, it has to be the same two (CW1 and CW2) handling the entire bundle, e.i, H8 and H9 both have the number "1" which should tell the macro/formula that whoever caseworker (CW1 and CW2) is generated for I8 and I9, they have to be the same because they are being treated as a bundle. In the spreadsheet you can see that Daniel is assigned as CW1 and Charlotte is assigned CW2 (I8,I9 and J8,J9 respectively). I manually changed that from the dropdown list to show you what I mean.
So, I would like to build a macro that can tell which numbers belong together (1,1 and 2,2,2, and 3,3,3,3 etc.) and assign the same CW1 and CW2 for the entire bundle. The assigned caseworkers should be random so the same two caseworkers aren't doing all the work.
Those cells in column "H" that have been left blank can be assigned to any caseworker since they are individual cases. You can easily select the two or more caseworkers manually from the dropdown list, but I was hoping it could be done automatically since some days we have over 1000 cases.
Is this possible? Thank you so much :D
I can post a link to the spreadsheet upon request. Just let me know in the comment box below, thank you!
I've been using this forum a lot to get inspiration to build a spreadsheet for work that is both functional and simple to use. I realized that "simple" features in Excel often have complex functions and/or codes (VBA) that you have to master before enjoying your hard work.
I'll try to explain to the best of my abilities with a little background information, so here goes.
I've built this caseworker assignment sheet (attached) that randomly selects a caseworker to cases by using one of the macros "Assign CW1, Assign CW1 Uneven Weeks, Assign CW2, Assign CW2 Uneven Weeks." You'll find the buttons in the "Assign" sheet. (Column M2)
Here is where it gets a little tricky.
Some of these cases are "bundles," (See column H) which means they should be processed by the same caseworker (CW1 and CW2) for easy processing. I have assigned a number to those who need to be processed together in column H.
Based on the numbers listed in that column, I want to choose a random CW1 and random CW 2 - just like I did the first time when I assigned them - to process those cases; HOWEVER, it has to be the same two (CW1 and CW2) handling the entire bundle, e.i, H8 and H9 both have the number "1" which should tell the macro/formula that whoever caseworker (CW1 and CW2) is generated for I8 and I9, they have to be the same because they are being treated as a bundle. In the spreadsheet you can see that Daniel is assigned as CW1 and Charlotte is assigned CW2 (I8,I9 and J8,J9 respectively). I manually changed that from the dropdown list to show you what I mean.
So, I would like to build a macro that can tell which numbers belong together (1,1 and 2,2,2, and 3,3,3,3 etc.) and assign the same CW1 and CW2 for the entire bundle. The assigned caseworkers should be random so the same two caseworkers aren't doing all the work.
Those cells in column "H" that have been left blank can be assigned to any caseworker since they are individual cases. You can easily select the two or more caseworkers manually from the dropdown list, but I was hoping it could be done automatically since some days we have over 1000 cases.
Is this possible? Thank you so much :D
I can post a link to the spreadsheet upon request. Just let me know in the comment box below, thank you!