using Harvey ball status to drive a formula

irene

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

Chris McKay

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:

1. 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.
2. 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
3. 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

irene

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

Chris McKay

Great to hear! Best of luck with the rest of your Smartsheet solutions.