Automate Harvey Balls Based on Date in a Column

Adam Kinney

Hello,

This may have been asked previously however I cannot seem to find my specific request that only reference if a date is entered (not range related) so here goes.  I have a Status column on a sheet and have it set to symbol: Harvey Balls.  What I would like the sheet to do is to automatically insert the correct Harvey Ball based on if the reference column that has a date (any date) or not.  Leave the cell blank if no date, Harvey Ball if any date is entered in the column.  Assuming an IF formula would do the trick but need help with that.

Initial Call = Empty

Initial Presentation = Quarter

Survey = Half

Final Presentation = Three Quarter

Conversion Scheduled = Full

Adam Kinney

Attached an image showing date fields populated and the associated Harvey Ball I would like to automatically appear in the status column.

Shaine Greenwood

Hi Adam,

I believe that you're looking for a formula like the following:

=IF(ISDATE([Conversion Scheduled]@row), "Full", IF(ISDATE([Final Presentation]@row), "Three Quarter", IF(ISDATE(Survey@row), "Half", IF(ISDATE([Initial Presentation]@row), "Quarter", IF(ISDATE([Initial Call]@row), "Empty")))))

This formula will reference cells on the same row (hence the @row variable) and will set the harvey ball status based on whether a date exists in the respective cell.

Adam Kinney

Thank you. Returning a #UNPARSEABLE error. saw Survey was missing brackets but still returning the same error.

Adam Kinney

@Shaine Greenwood got it to work using:

=IF(ISDATE([Qualified Win]@row), "Full", IF(ISDATE([Final Presentation]@row), "Three Quarter", IF(ISDATE([Survey]@row), "Half", IF(ISDATE([Initial Presentation]@row), "Quarter", IF(ISBLANK([Last Activity Date]@row), "Empty")))))

Need to add one more statement to cover [Verbal Agreement]@row the new column layout.