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

Find more posts tagged with

Formulas

Project Management

Comments

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:

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

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…