Using nested IF(AND) with ISBLANK and NOT(ISBLANK)

Hello,

I seem to have stumped myself. I have a dataset where we enter each date value in a separate column (drafting gets a date, when the document starts routing that gets a date in a different column, when the document is issued that gets a date in yet another column), like shown below.

I would like to figure out a way to have a separate column that summarizes all of that--ideally one that says (at minimum) "drafting," "routing" or "issued"--so in this example rows 47-50 would say "issued," while row 42 would say "routing" and row 43 would say "drafting" or "draft review."

Is there a way I can do that with MAX and MATCH or INDEX and have the max value in a given row report the column name?

Alternatively I was trying to set up a nested IF(AND function using ISBLANK and NOT(ISBLANK) (ie, if issued is not blank it's issued, if issued is blank but routing is not blank it's routing, etc. This is what I attempted (to set up the "routing" value) but it's showing as unparseable and I can't quite figure out where I've gone wrong.

=IF(AND(ISBLANK(Issued@row), NOT(ISBLANK(Routing Start Date@row))), "ROUTING")

Is there a simpler way to do this and I'm overthinking it?

Thank you in advance for any help you can give!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Will the dates always be filled out in a specific order?

Tracy.Kenna

https://community.smartsheet.com/discussion/comment/359141#Comment_359141

Yes, they always go in order from drafting --> routing --> issued.

Paul Newcome

In that case you could use a nested IF and work backwards.

=IF([Last Date]@row <> "", "Completed", IF([2nd to Last Date]@row <> "", "Last Step", IF([3rd to Last Date]@row <> "", "2nd to Last Step", .........................)))

Tracy.Kenna

https://community.smartsheet.com/discussion/comment/359150#Comment_359150

OK, thank you! I'm really sorry, but I'm rather new to Smartsheets and I don't entirely understand what you're suggesting. I'm trying to extract the data from my original screenshot into a standalone column that summarizes it all, like this. Are you saying I set it up so that IF[Last Date]@row>[Second to Last Date], "Completed", .... or am I total misunderstanding? Thank you for your help and sorry for the confusion.

Paul Newcome

No worries.

You would write out a nested IF that basically says...

If the last date is not blank, "Complete". If the second to last date is not blank, "Last Step". If the third to last date is not blank, "Second to Last Step".

If it is easier, you can also work forwards and evaluate for is blank.

If the first date is blank, "First Step". If the second date is blank, "Second Step". If the third date is blank, "Third Step".

=IF([First Date]@row = "", "First Step", IF([Second Date]@row = "", "Second Step", IF([Third Date]@row = "", "Third Step", .................)))

Tracy.Kenna

https://community.smartsheet.com/discussion/comment/359266#Comment_359266

Thank you so much! I played with it more over the weekend and managed to set it up so that basically if last date was greater than second to last date, it marked it as complete and so on. Your way sounds a bit more elegant, I will have to play with that and see if I can simplify it.

Thank you for your help!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.