May 30 2023 10:43 AM
May 30 2023 10:43 AM
I am trying to add a function on multiple tables with drop-down menus that will make sure I don't input the same option in the same spot on the table.
For example, if I place Gym 1-2 Monday at 9:00, I can't put Gym 1-2 in the same time slot on another table.
I would like a function that looks for this and puts both cells in Red to let me know if there was a mistake made.
May 30 2023 01:20 PM
May 30 2023 04:11 PM
@mathetes Hey ! Here is a link to google drive with a copy of the excel file
Need to have every monday first period look at the other tables to see if theres a duplicate, if theres a duplicate, to place each "error" cell red to tell me theres something that doesn't work.
So B4;I6;P4;W4;AD4;AK4;B19;I19;P19;W19;AD19;B34;I34;P34;W34;AD34;AK34;B49;I49;P49;W49;AD49 are all the cells for Monday first period.
May 30 2023 06:19 PM
I understand what you've asked for--that you want the cells for any given period on any given day to NOT show the same activity, so if they do, you want it to show in Red.
What I don't understand is why. It seems backward. Just to look at the GoogleSheet sample, it would seem you desire having the same activity in the same period each time; i.e., that the error would be if something else were to be entered. So maybe I don't understand what this spreadsheet is supposed to be doing in the first place: it looks as if you are scheduling activities or classes or _________, where, again, it would seem to make sense that the same activity often (if not always) be repeated in the same period on a Monday (or whatever).
So could you explain more fully what the bigger picture is here?
Quite frankly, it looks to me as if you're also using Excel largely for it's rows and columns, and not in general for any calculations or anything really using computer power -- with the possible exception of highlighting what you're calling an error. That's fine, but .... well, as noted, I'd appreciate a more complete description of what this is to be used for, how entries will be made, what will the output be, etc.
May 30 2023 07:06 PM
May 30 2023 07:08 PM
May 31 2023 07:57 AM
what I'm trying to do is a schedule for 23 different groups.
Each table on the sample sheet I shared is for 1 week. Meaning, I DONT want the same activity in the same period of each time.
OK, that makes sense. Let me see what I can do.
May 31 2023 09:09 AM
The only way I'm aware to accomplish what you're trying to do without some elaborate VBA routine (which is an aspect of Excel at which I do NOT Excel)....is by means of Conditional Formatting. That IS possible, but will require an extensive definition of the conditions.
To illustrate, I've created a set of Conditional Formatting rules that do what you want in cells B4, I4, P4, W4, AD4, AK4 and AR4, i.e., the Monday/Lundi columns across the top set of your "tables."
Here's that rule as it applies to B4. It gets adjusted appropriately for each of the others in that row
I added in that condition of B4<>"" because otherwise two blank cells were considered to be the same and highlighted in red.
Making this more complete, though, you also want this rule to apply to the following cells: B19,I19,P19,W19,AD19,B34,I34,P34,W34,AD34,AK34,B49,I49,P49,W49,AD49
and, unless I'm mistaken, you'd actually want the rule to apply vertically as well--e.g., you don't want "Salle Poly" more than once in any given day. Right?
So check out the attached sample spreadsheet for Row 4 and the Monday/Ludi columns...I think you'll find it works. The question is, do you want to extend it to all the others? If so, I'll leave it to you to extend that. It's relatively easy to use the Copy Format command from cell to cell, but it'll still be up to you to edit it for applicability.
It may be that some of the VBA experts in this forum would have another approach altogether. Let see if any chime in.
May 31 2023 12:04 PM
May 31 2023 12:39 PM
But is there a way to apply the formula to each cell without having to do it manually for each of the 805 cells i need to add a condition?
Once I had created that formula in Cell B4, I used the PaintBrush icon in the Home toolbar
to copy the Formatting instructions across to all the other Monday/Lundi cells in that row, and made very simple modifications so that it did the same for I4 that it did for B4, etc. It didn't take long, once I'd started doing it, maybe 15 seconds per cell. I suspect that you'll have to do something like that, yes, in each of the 805 cells. I'll be very happy to be contradicted in that suspicion. At worst that would be maybe three to four hours; I suspect that will be worth it if you're using this a lot and frequently.
It was satisfying to discover a way to do it using Conditional Formatting, a way that actually works cleanly. The underlying challenge here is that you are using Excel in what I'd call an idiosyncratic way, more for its cellular structure--the rows and columns of a display-- than for actual Excel strengths (calculations, data manipulation, etc.).
May 31 2023 12:46 PM
May 31 2023 12:52 PM
May 31 2023 02:17 PM - edited May 31 2023 02:19 PM
The simple change was modifying the one reference that referred to a cell that wasn't a Monday/Lundi cell. So in copying across, every column reference incremented by 7, so that what had been AR4 became AY4. All I had to do was change that AY4 to B4.
So, specifically, the formula in the Conditional Formatting dialog box for B4 is
and in I4, after changing the final I4=AY4 to I4=B4 it reads
You just need to try a couple and go slowly at the start, testing the result before you move on. Because all the references are "Relative" references rather than "Absolute," all of the other B4s had changed to I4s on their own.
If you run into trouble after giving it a good try, come back with what you've done and I'll see if I can figure out what's giving you the difficulty and how to make it work.
May 31 2023 06:00 PM