Forum Discussion
Trying to add a function to multiple tables with drop down menus
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.).
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.
- "and made very simple modifications so that it did the same for I4 that it did for B4, etc"
What were the simple modifications that you needed to do, just adding the I4=B4 inside the formula? - Yes, the reason I am using excel is because no other program that I know of can do what I want to do and Excel can do it rather efficiently for me 😄
Instead of doing it physically with paper 😄