using Harvey ball status to drive a formula
Hi,
Is there a way to use the harvey ball status to drive a formula?
Here's an example of what I was able to achieve in excel but cannot replicate in Smart Sheet. =If(HarveyBall = "Full",date-today(),"")
Result in excel, I get blank if result is true
Result in SmartSheet, I get #Unparseable
Observation is that the HarveyBall status doesn't seem to convert to a value properly whether text or percentage. Help!
Thanks,
Irene
Comments
-
Hi Irene,
Assuming the =If(HarveyBall = "Full",date-today(),"") formula you posted is what you've actually entered into Smartsheet, there a couple of issues:
- You will need to tell Smartsheet what row the Harvey Ball (and your date) resides in. It does not assume that you are looking in the same row, as you might want to check the row above each row the formula resides in, or the same row for each instance.
- Unless the date you're wanting to return is in a column named date-today(), your formula will do nothing (i.e. return an error) which is why you're getting #UNPARSEABLE
- I'm also going to assume that the column that the formula resides in has not been formatted as a Date column. Even if date-today() was correct, the resulting value returned will be a date and therefore the IF formula column will need to be a Date column
Based on this and assuming you want the Harvey Ball in the same row referenced, your formula will need to look something like this:
=IF([Harvey Ball]@row = "Full", TODAY(), "")
But bear in mind, that TODAY() will pull the current date (i.e. it will keep updating every day) regardless of whether the Harvey Ball is full. This does not create a snapshot to capture the TODAY() date that the Harvey Ball was updated to Full.
Hope this helps.
Kind regards,
Chris McKay
-
Hi Chris,
Brilliant explanation and #1 works for me. that was the one step I didn't attempt. After Ihave updated the formula to target the harvey ball rows rather than generically across column, the formula returned the results expected.
Many thanks!
Irene
-
Great to hear! Best of luck with the rest of your Smartsheet solutions.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!