conditional formatting to check every nth of column

0
votes

I try to add conditional formatting using custom formula. I want to check if in a single row (in this case, in row 5 and it starts from column L and skip every 3rd column: L, P, T,....), there's at least one not empty shell.

The formula below works perfectly. It's only it's not dynamic, I mean in case I add more column to check then I have to add more and more "+ not(isblank(...))" there and become very long. Is there any shorter formula which should be checked no matter the last column is. 

=(not(isblank(L5))+ not(isblank(P5))+  not(isblank(T5)) +not(isblank(X5)) + not(isblank(AB5)) +not(isblank(AF5)) + not(isblank(AJ5))+ not(isblank(AN5))+not(isblank(AR5))+not(isblank(AV5))+ not(isblank(AZ5))   ) > 1

here's the link: https://docs.google.com/spreadsheets/d/1kedGsLIUw2UsA8LbiWe0w_s9HYoBLqOl6zGQxZlLq5s/edit#gid=0

1
google-sheetsgoogle-sheets-formulags-conditional-formatting
Thanks for replying but .. sorry, do you think sharing a copy of the sheet is good option? because when later i delete the sheet then it will be broken link, so the solution (knowledge) will be useless for other people. don't you think ? – andio
"do you think sharing a copy of the sheet is good option?" I doubt you would ask if you had first looked at the reputation points for @player0. In any event, the answer to your question (from a mere novice) is "Yes, it is a good option" It's in your interest to provide users with as much information as possible - sharing a sheet is an absolute necessity. "Will it be useless in the future?" Your sheet may be gone, but the answer (if any) provided will remain, and it often happens that users include a snapshot (as well might you), to show the proof of their answer. – Tedinoz
Hi Tedinoz don't get me wrong, i'v never doubt @player0, in fact he has answered a lot of my previous questions. To be precise, He answer most of all my google sheet qustion. But sharing something that is not static can be confusing. It happens to some of my post in other thread when i give example by share link from imgur/photobucket or other image server even share them (publish) from my own server. I deleted them and i realize when i revisited my post all the images are gone. Anyway, i will share the sheet, it's just my thought about the community. – andio
well, to be honest, I don't understand your question which means that I can think of several solutions from which only the one is good for you. the issue is that I have no idea which one is the right one. therefore sharing your scenario of the dataset (a copy of your sheet) with an example of desired output could (not always) help me (or someone else) to better understand what exactly are you after. of course, it's up to you - I don't need anything. maybe someone else will find your question and understand it in such a way that he/she will think of only one possible solution - the one you need – player0
@player0 hi i just shared the link. Hope that's help. I want to flag the ID when the there's a note , but flag to red if there is multiple notes. the conditional formatting is works perfectly, it's just way too long if i want to add more and more the 'note' column. I think should be a way to check every nth column rather than manually state them one by one. Thanks a lot. – andio

1 Answers

0
votes

you can do it like this which is shorter but still not dynamic:

=((E2<>"")+(I2<>"")+(M2<>"")+(Q2<>"")+(U2<>"")+(Y2<>"")+(AC2<>"")+(AG2<>"")+(AK2<>"")+(AO2<>"")+(AS2<>"")+(AW2<>"")+(BA2<>"")+(BE2<>"")+(BI2<>"")+(BM2<>"")+(BQ2<>"")+(BU2<>"")+(BY2<>"")+(CC2<>"")+(CG2<>"")+(CK2<>"")+(CO2<>"")+(CS2<>"")+(CW2<>"")+(DA2<>"")+(DE2<>"")+(DI2<>"")+(DM2<>"")+(DQ2<>""))>1

this will cover 30 columns (the equivalent of range A1:DQ which is 121 columns)

but if you need more I created a generator in your sheet where you just input number of columns and it will create you the formula you just copy-paste in Conditional formatting:

0

tho still not truly dynamic solution but hey, better than a nail in the eye