Cell validation with limits and multiple formulas

[sandyandy5]

Hi! First time poster disclaimer to start with!
I’m creating a nursing shift roster and wanting to limit only 2 ‘D’ shifts per day then to lock others out. I dont know what formula I should be putting into the custom cell validation to achieve this. My current formula is =SUMPRODUCT(COUNTIF(D$4:D$16, Vailid!$E$2:$E$168)).
Any help or ideas of how to make this work would be greatly appreciated!cheers

 

[KRice]

If a problem becomes difficult to sort out using the tools available in the forum, a link to the file is sometimes posted. Are you able to upload it Dropbox, Box, Google Drive, or similar service so that a sharing/download link can be posted? Just be sure to share only the link to the file...which can be pasted directly into your post in this thread.

 

[sandyandy5]

If a problem becomes difficult to sort out using the tools available in the forum, a link to the file is sometimes posted. Are you able to upload it Dropbox, Box, Google Drive, or similar service so that a sharing/download link can be posted? Just be sure to share only the link to the file...which can be pasted directly into your post in this thread.

Sorry just had to download dropbox. I've never used it before so have just uploaded the whole thing with out taking out names. Hope this comes through ok?

Roster debugged familarisation.xlsx

Shared with Dropbox

www.dropbox.com

 

[KRice]

It did...thank you. I'll have a look.

 

[KRice]

I uncovered something. Here I've clicked on the cell for Wed the 6th in the Leadership section of the Roster table...the 3rd column in the fillable scheduling table, so this should correspond to the 3rd column in the helper column, which would be Valid!O...and since this is the Leadership section, the topmost helper cells would be Valid!O$3. But the Data Validation here is pointing to Valid!$M$3. Only the row should be fixed with a $ sign, while the column is left relative (no $ sign in front of it). This way, the Data Validation formula can adjust, meaning as it is copied across the roster table, the topmost helper references will also change so that they point to the correct helper column.

But there is another issue: including the SUM(N construction as the 4th argument seems to cause a problem, probably because this needs to be handled as an array formula to first create the array formed by the logical test before converting the TRUE's and FALSE's to 1's and 0's and then summing the array. A quick test is to replace that 4th argument with 17 (the number initially returned if there are no shift code exclusions), and the full dropdown list appears when attempting to fill a cell on the scheduling table. There are several ways to deal with this, but the simplest is to avoid it altogether and add something to your helper tables. At the top of each helper column, I added a formula that performs this same counting operation...to determine the number of valid shift codes in that column (for that specific day). By doing this, the OFFSET formula can be simplified, so the uppermost anchor point where the shift codes begin for the Leadership block is Valid!M$3, and if we now look above it in Valid!M$1, the total count of non-blank entries in the shift code column is found. Then the OFFSET formula changes to this:
...for the Leadership block

=OFFSET(Valid!M$3,,,Valid!M$1,1)

=OFFSET(Valid!M$67,,,Valid!M$65,1) for the RM block
=OFFSET(Valid!M$24,,,Valid!M$22,1) for the Graduates block
=OFFSET(Valid!M$45,,,Valid!M$43,1) for the Students block

As before, this data validation (DV) formula is placed in the upper left cell of each relevant scheduling block, the cell is copied to the clipboard, and then the entire block is selected and a Paste Special > Validation is performed to quickly assign the same DV to all cells in that block.

I didn't touch any other cells other cells (other than moving the helper block labels aside to make room for the adding counting formulas, modifying the DV OFFSET formulas and copying the DV throughout the roster table blocks). But as I look at some other areas below the roster table, I see the Daily Count tabulation block (rows 92:96). I would consider removing the blank rows. Doing so makes it easier to refer to an entire range of {D;E;N} values without any blanks, and that has advantages if one day you want to streamline some of the calculations. For example the Shift Short By formula would be C98:C100-D94:D96 to return all three subtraction values (assuming the D92, D94, and D96 values actually resided in the contiguous range D94:D96. This isn't important at the moment, but if you move the workbook to Excel 365 where array formulas are handled automatically, there are efficiency and formula maintenance advantages.

I'll give some more thought to this...there may be a better way to either construct or reference all of these dropdown helper columns, but I'll have to try out some ideas. In the meantime, let me know if this works on your system, and whether you encounter any more issues or have any questions. Here is a link to the revised version:

Dropbox

www.dropbox.com

 

[sandyandy5]

I uncovered something. Here I've clicked on the cell for Wed the 6th in the Leadership section of the Roster table...the 3rd column in the fillable scheduling table, so this should correspond to the 3rd column in the helper column, which would be Valid!O...and since this is the Leadership section, the topmost helper cells would be Valid!O$3. But the Data Validation here is pointing to Valid!$M$3. Only the row should be fixed with a $ sign, while the column is left relative (no $ sign in front of it). This way, the Data Validation formula can adjust, meaning as it is copied across the roster table, the topmost helper references will also change so that they point to the correct helper column.

View attachment 65788
But there is another issue: including the SUM(N construction as the 4th argument seems to cause a problem, probably because this needs to be handled as an array formula to first create the array formed by the logical test before converting the TRUE's and FALSE's to 1's and 0's and then summing the array. A quick test is to replace that 4th argument with 17 (the number initially returned if there are no shift code exclusions), and the full dropdown list appears when attempting to fill a cell on the scheduling table. There are several ways to deal with this, but the simplest is to avoid it altogether and add something to your helper tables. At the top of each helper column, I added a formula that performs this same counting operation...to determine the number of valid shift codes in that column (for that specific day). By doing this, the OFFSET formula can be simplified, so the uppermost anchor point where the shift codes begin for the Leadership block is Valid!M$3, and if we now look above it in Valid!M$1, the total count of non-blank entries in the shift code column is found. Then the OFFSET formula changes to this:
...for the Leadership block

=OFFSET(Valid!M$3,,,Valid!M$1,1)

=OFFSET(Valid!M$67,,,Valid!M$65,1) for the RM block
=OFFSET(Valid!M$24,,,Valid!M$22,1) for the Graduates block
=OFFSET(Valid!M$45,,,Valid!M$43,1) for the Students block

As before, this data validation (DV) formula is placed in the upper left cell of each relevant scheduling block, the cell is copied to the clipboard, and then the entire block is selected and a Paste Special > Validation is performed to quickly assign the same DV to all cells in that block.

I didn't touch any other cells other cells (other than moving the helper block labels aside to make room for the adding counting formulas, modifying the DV OFFSET formulas and copying the DV throughout the roster table blocks). But as I look at some other areas below the roster table, I see the Daily Count tabulation block (rows 92:96). I would consider removing the blank rows. Doing so makes it easier to refer to an entire range of {D;E;N} values without any blanks, and that has advantages if one day you want to streamline some of the calculations. For example the Shift Short By formula would be C98:C100-D94:D96 to return all three subtraction values (assuming the D92, D94, and D96 values actually resided in the contiguous range D94:D96. This isn't important at the moment, but if you move the workbook to Excel 365 where array formulas are handled automatically, there are efficiency and formula maintenance advantages.

I'll give some more thought to this...there may be a better way to either construct or reference all of these dropdown helper columns, but I'll have to try out some ideas. In the meantime, let me know if this works on your system, and whether you encounter any more issues or have any questions. Here is a link to the revised version:

Dropbox

www.dropbox.com

It's amazing Kirk!! I've been at work and tested it out on their system and all seems to be working as it should be. I've populated our last roster throughout it also and have found I've need to increase the night shift capacity to 6 for the RM but this was an easy fix now that i know how to do it.
I would like to sincerely thank you for all of your invested time and efforts in seeing this through to the end in helping me (a complete novice excel user) bring it to life. What a fabulous and valuable resource 'MrExcel' really is!
Cheers and thanks again!!

 

[KRice]

That's great, Andrea...I'm happy to help. I was hoping that you would be able to check it out on your work system. I forgot to mention that I had initially inserted a Named variable with the Name Manager before changing my mind and abandoning that idea once the Data Validation Source formula was simplified. An artifact of that process, however, was left behind: the ddList item in Formula > Name Manager can be safely deleted, as it is not used anywhere. Continued maintenance will probably be tedious, but fairly straightforward. You have three types of formulas: the helper list top-of-column sums, helper formulas for creating day-specific drop down lists (one for each group of staff type), and Data Validation formulas (also one for each group of staff type). As people are added/removed from the roster, you'll need to confirm that all are still working as intended. Please let me know if you run into any difficulties, or if one day, you'd like to streamline this for Excel 365. You can always reach out to me via the "Conversations" button (the envelope icon immediately to the right of your account name in the upper right of the forum window) if I might be able to help clarify anything or if there is anything you'd like to direct my attention toward.

 

[smozgur]

@sandyandy5 - I see that you marked your final post as the solution, however it doesn't contain the answer, so I unmarked it. Could you please mark the post that actually (best) answered the initial question in this thread, so it helps future readers?

 

[KRice]

...most likely was intended to be Post #64 with the link to the debugged version of the workbook

 

[smozgur]

...most likely was intended to be Post #64 with the link to the debugged version of the workbook

Thanks for confirming @KRice. It is a very long thread containing 64 posts, so I wanted to make sure.

 

[KRice]

Thanks Suat. Yes, it is long, and probably difficult to follow. The earlier posts described several formulas that are needed to address the initial question, where the emphasis is on creating dropdown lists in helper columns based on multiple conditions, and then those helper columns are used for Data Validation. Revisions were needed to accommodate using this on a system running Excel 2013. A majority of the later posts were focused on debugging to make sure the formulas pointed to the correct ranges. All of these things are best brought together in the debugged file shown in post 64. I appreciate your help marking that one.