How to check multiple cells via a formula

PJay128

I have two columns one which is a tick column the other is a date column.

I want to add a status column which will update ryg balls.

So if the value in the tick column is 1 i get a green ball if the value in the date column is not blank i get a green ball. And if neither of them have any value the ball is red.

I don't need to have both values to get a green ball just one.

And ofc if i have no value in either i get a red ball.

Cheers

 

Andrée Starå

Hi,

Try something like this.

=IF(AND(Tick@row = 0; Date@row = ""); "Red"; IF(OR(Tick@row = 1; (ISDATE(Date@row))); "Green"))

The same version but with the below changes for your and others convenience.

=IF(AND(Tick@row = 0, Date@row = ""), "Red", IF(OR(Tick@row = 1, (ISDATE(Date@row))), "Green"))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Did it work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

PJay128

Yup Worked.

I got a flipping OR statement in my head and couldnt shake it!

Thankyou

:P

Andrée Starå

Excellent!

Happy to help!

You were partially correct, and there is an OR in there.

Best,

Andrée

Paul Newcome

I feel like you could also get away with JUST the OR statement for this one as well since you only have two values being populated...

 

=IF(OR(Tick@row = 1, (ISDATE(Date@row)), "Green", "Red")

 

It basically says that if either of them are true, then do green. Everything else will give you a red.

Andrée Starå

Paul,

Yes, you're correct!

They both work! PJay was right in thinking that it should be an OR. 

Best,

Andrée

Paul Newcome

I have been catching myself over-complicating things more and more lately. I've been trying to get back to the most simple working solution. I just happened to see this thread in the middle of trying to simplify some other projects I have going on.

 

I recently built a sheet that has so much automation built into it, that Chrome, Edge, and Firefox all crash if I have that sheet and even one other tab open. Granted I am working on a SurfacePro, so there isn't a ton of memory to begin with, but still... If the sheet can't be used when only about 2/3 of the automation is complete, it may be time to start trying to simplify. 

Mike Wilday

@Paul - I am working on a macbook pro with more than enough memory... and some of our sheets do the same thing... It might be more than your automation that's causing the problem.  But I do value your mindset for simplification. 

Andrée Starå

I've fallen in that trap as well! 

 

Paul Newcome

@Mike - I have just shy of 100 "helper" columns. 12 of them are for various tables. The rest are complex date and totals calculations based off of checkboxes and dates that are pulled via x-sheet references which automate yet more data that loops back to the other sheet. It also happens to be about 850 rows. Hahaha.

 

Opening in Google Chrome (I know it's a notorious memory hog, but still...) puts a 4.9GB draw on my memory. I only have access to 7.9 to begin with. Hahaha. Even if I upgrade, the rest of my team would have to upgrade and so would some clients just to be able to use the sheet, and you know how people are when you start talking tech upgrades (especially considering this is my second Surface in a year).

 

I don't know if there is an issue on the SS side, with my Surface, or if it's a combination of both. I have a guy from my IT department trying all kinds of crazy mess to try to make this sheet usable as is.

 

It's a MONSTER. 

Paul Newcome

Hahaha. Glad to know I'm not the only one that does it.

Andrée Starå

You're not alone! 

Mike Wilday

Reminds me of this... see attachment. 

Paul Newcome

HAHAHAHAHA!!!!! Sounds about right.

 

I asked for a lightning fast computer with an 8 foot display that registers hand and eye movements instead of using a mouse and keyboard. I found a great setup for only $20,000.00, but they said it just was not in this year's budget.

 

I'll ask again next year.