How to check multiple cells via a formula
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
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Yup Worked.
I got a flipping OR statement in my head and couldnt shake it!
Thankyou
:P
-
Excellent!
Happy to help!
You were partially correct, and there is an OR in there.
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Paul,
Yes, you're correct!
They both work! PJay was right in thinking that it should be an OR.
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
@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.
-
I've fallen in that trap as well!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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.
-
Hahaha. Glad to know I'm not the only one that does it.
-
You're not alone!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Reminds me of this... see attachment.
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives