Magic Scientist Excel Brain Help? If B2-P2 are blank (and) Q2 isn't then make A1 fill red

need help excel.PNG

I need some magic scientist excel help with this one. I *think* than logic (if then) would work with this, but I am not smart enough to figure it out.

I am also working with 180K rows of data. (By the way, if I am working with this large of a file, should I back it up with SQL?)

The riddle is: If cells from B2 to P2 (and all the 180K rows beneath those) are empty, but cell Q2 is not....then...(I would like a quick way to identify this. For me, the best way is to fill the cell in A with red & make the font white.

I've attached a screenshot for clarity.

THANK YOU excel family!!!

Try select data range in column A2:A180000(last row of your data), and create new conditional formatting as follow and set the color/font accordingly:

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Should I enter a value in this part? "",Q2<>"")

Not necessary, it's written in a way that matched your criteria
Does it works?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Ah ok. No, it didn't.

since you pointed out that conditional formatting may work, I will read up on it. I would like to set up other rules too, like: if column f, g, and h all have a value then (somehow point that out).

I'm dealing with so much data....I am trying to find patterns. I need to see which columns have the most data across a row...

It would be even better if I could cross check that against column q.

For example, if several columns have data in one row, but q is empty, I want to know this first and foremost.

Not sure if my description makes any sense. I guess, can I ask, do you recommend I learn more about conditional formatting? Oh, and would you recommend me using something else? (sql). ... I am driving in the dark here.

Originally Posted by FDibbins

that wont work, try this instead...
=AND(counta(B2:P2)=0,Q2<>"")

Hi Ford, tested formula in post#2 with yours and both yielding same result.
Did I miss out something



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi colleendeborah, I would definitely recommend you to read up more about CF as it's very useful (even though you might not be using for this problem).

As Ford suggested, creating helper column(s) would be a good alternative and all depends on how huge is your data, and how complicated for the criteria that you need to accomplish your goal



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi Ford,

Ah I see, yea using merely range without function seems like returning unforeseen result

Following probably yield consistent/expected outcome

Formula:

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin