Hi Andrea, I took a peek at your other thread (hope you find some good advice about how to implement a VBA script)...I think that may be the only way to do both--use formulas for creating data validation lists and Data Validation on the inputs while also preventing overwriting of roster sheet cells.
But something struck me when I saw your OFFSET formulas for two of the table blocks. Here is what mine looks like:
Excel Formula:
=OFFSET(W$3,,,SUM(N(W$3:W$11<>"")),1)
The OFFSET function is used to select some range of interest on a worksheet and it takes 5 arguments. The 1st is the anchor point for the range to be selected...that is, some knowable cell that makes sense. The 2nd and 3rd arguments describe how many rows and columns to step away from this anchor point before we begin aggregating cells into a range. In our case, we want to specify the top cell of the day of interest in the helper table. In my case, that cell is W$3 and in your last post, I am guessing that you have a helper table whose top dropdown data item resides in Valid!M$45. Then we are not interested in stepping away from this anchor point...we want our range of cells to be collected beginning right at the anchor point, so the 2nd and 3rd arguments are to be ignored...that is why the formula has a set of triple commas, where you can see that a row offset number would fit between the first pair in that triplet and the the column offset number between the second pair in that triplet of commas. Then we move to the 4th and 5th arguments, which represent the height and width of the range to be selected. The height is the number of nonblank cells in the helper table column
for that same day. So in my example above, I used the construction SUM(N(W$3:W$11<>"")). The W$3:W$11 part represents the entire column range of my helper table for some specific day. But all of those cells may not be populated with shift codes because some have been removed by the other formula. To determine how many cells are not blank I perform a logic test W$3:W$11<>"" which will produce an array of TRUE's and FALSE's in the same order as W3<>"", W4<>"", W5<>"", etc. (W3 is not blank, True or False?, and so on for each logical test). This array of TRUE's and FALSE's is converted into 1's and 0's using the N function, so the entire TRUE/FALSE array building part of formula is wrapped inside an N function. And finally that array of 1's and 0's is summed. That sum represents the height of the range that the OFFSET function should select. And then we want OFFSET to return only this single column so the width of the range to return is 1. The key takeaway is that the 1st argument in the OFFSET function (my W$3 and your Valid!M$45) needs to be the same starting point for constructing the nonblank-counting SUM formula in the 4th argument. That's why my logical test is W$3:W$11<>"" (note the W$3 stating location, extending down to W$11). But your 4th argument is counting some other range that is not related to Valid!M$45...you have Valid!M$3:M$19<>"".
Let me know if this is clear enough to help you do some debugging. I suspect this is why you're getting some odd results at times. If you do have a dropdown column beginning in Valid!M$45, what is the lower cell reference of that same dropdown column? That's what you will want for the 4th argument. Pay careful attention that what gets "fixed". You want only the row numbers fixed, not the columns, so the $ signs appear before the row numbers when the ranges are specified.
