Forum Discussion
Trying to add a function to multiple tables with drop down menus
mathetes Hey ! Here is a link to google drive with a copy of the excel file
https://docs.google.com/spreadsheets/d/1IgbiQhkVmU4Su5o4kD0AirYI8qvJgsc4/edit?usp=sharing&ouid=116265735144766116399&rtpof=true&sd=true
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.
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.
- I'm available if you are to hop in a discord call and give more details and maybe a clearer explanation. Having a tool like this would make it a LOT easier for me as I am currently doing it manually with physical paper and such 😕
- Hey, so 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.
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.- see if theres a duplicate,
I would prefer data validate to RED color like below
https://techcommunity.microsoft.com/t5/excel/data-validation-multiple-criteria-ticketing-system/m-p/3773899 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.