Forum Discussion
Trying to add a function to multiple tables with drop down menus
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.
For example, if group A has gym 1-2 during the first period on Monday, group B CANT have gym 1-2, that's why, if that happens, I placed both groups doing the same activity at the same time of day, I want it to appear as RED so that I can see that there's an overlap and I need to fix it.
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.
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.
- Hi, this is EXACTLY what i want it to do,
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? xD
Also, if i want to add those cells for the monday line: B19,I19,P19,W19,AD19,B34,I34,P34,W34,AD34,AK34,B49,I49,P49,W49,AD49
would the formula look something like:
=(AND(B4<>"",(OR(B4=I4,B4=P4,B4=W4,B4=AD4,B4=AK4,B4=AR4,B4=I19,B4=P19,B4=W19,B4=AD19,B4=AK19,B4=AR19,B4=I34,B4=P34,B4=W34,B4=AD34,B4=AK34,B4=AR34,B4=I49,B4=P49,B4=W49,B4=AD49,B4=AK49,B4=AR49)))) ??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.).