IFERROR not working

This seems pretty straight forward for me but it's not working. I want to add an IFERROR to this formula which is determining and displaying a status of several children steps in a process. I don't want the INVALID COLUMN VALUE to show initially as it does now, until they enter a date. I want it to be blank until they enter a date. :-)

=INDEX(COLLECT(CHILDREN([Task Steps]@row), CHILDREN([Task Started]@row), ISDATE(@cell) = "True"), COUNT(COLLECT(CHILDREN([Task Steps]@row), CHILDREN([Task Started]@row), ISDATE(@cell) = "True")))

This isn't working:

=IFERROR(INDEX(COLLECT(CHILDREN([Task Steps]@row), CHILDREN([Task Started]@row), ISDATE(@cell) = "True"), COUNT(COLLECT(CHILDREN([Task Steps]@row), CHILDREN([Task Started]@row), ISDATE(@cell) = "True"))), " ")

Find more posts tagged with

Accepted answers

Paul Newcome

I have another idea...

=IF(COUNTIFS(CHILDREN([Task Started]@row), <> "") = 0, "", INDEX(CHILDREN([Task Steps]@row), COUNTIFS(CHILDREN([Task Started]@row), <> "")))

All comments

Paul Newcome

What type of column are you putting the formula in?

Jeana

It's Text/Number field and it's not the Primary field.

Thanks

Paul Newcome

Can you double check that?

Are you able to provide screenshots with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?

Jeana

See below, the formula works in above scenario. What I'm trying to do is have the cell be BLANK until the dates are entered to start the tracking. The error message only appears when there are not dates in the fields.

Thanks

Screen Shot 2020-05-11 at 9.36.50 AM.png

Jeana

I'd like to use IFERROR to eliminate both INVALID messages that appear until the tasks are started and dates entered.

Thanks

Paul Newcome

Why isn't the second formula in your original post working? Are you still getting an error? You should be able to use...

=IFERROR(original_formula, "")

Jeana

Screen shot of IFERROR.jpg

Jeana

The way it's set up now it shows the error until there is a date in the first and second date columns. The header is cut off but the date columns are Start Date, End Date and Due date. When a date is entered it updates the two formulas that are showing the error. One shows the status of the job in general, "In Progress", "Job Completed", or "Not started". The other show the what step in the progress the request is in. Does that help? Appreciate your assistance!

Jeana

Paul Newcome

Ok. The IFERROR when used as specified above should take care of the #INVALID VALUE error (tested and confirmed).

The other error of #INVALID COLUMN VALUE... Exactly what column type is that?

Jeana

I tried this formula - =IFERROR(INDEX(COLLECT(CHILDREN([Task Steps]@row), CHILDREN([Task Started]@row), ISDATE(@cell) = "True"), COUNT(COLLECT(CHILDREN([Task Steps]@row), CHILDREN([Task Started]@row), ISDATE(@cell) = "True"))), "")

for the Tasks Status and I get #INCORRECT ARGUMENT SET.

I don't see what I'm doing wrong? :-/

Paul Newcome

I don't see what could be causing the argument set error. Hmm... Let's try this...

Forget about the formula for a minute and let's take a look at your process. What exactly are you trying to accomplish based on what criteria?

Jeana

So the workflow is this. A form is used to take in a request and it goes to a REQUEST sheet. The TRACKING sheet (the one I'm struggling with) is used to track the progress of an asset. So when a new request is made an email is sent back to the requester with the Asset #. They go to the TRACKING sheet and enter the Asset # in a placeholder. This placeholder parent row pulls in the related information from the REQUEST sheet (using Index/Match and Data Mesh). The Children for this Parent (the Asset) is used to track the steps or progress of the asset. Different people will come into this sheet and simply enter a date for the appropriate action they have taken. Sometimes adding comments and file attachments.

The Task Status formula on the PARENT row is checking to see what CHILD row has an End Date and it updates the status or what step in the process it's in based on a date being entered or not.

The Request Status is an overall status to show if the Asset is still in progress, completed or not started. Also based on a date being entered in the Task Done Children field.

Hope that helps to make more sense out of what I'm trying to do.

Jeana

Paul Newcome

Ok. I have a few ideas. Can you take the screenshot you provided (shown below) again except include the column names in the screenshot as well?

Jeana

Screen Shot 2020-05-12 at 9.58.09 AM.png

Jeana

Screen Shot 2020-05-12 at 10.02.10 AM.png

This show one of the 'placeholder' sections where the user enters the Asset # and the parent info is pulled over. Then they start tracking by entering dates in Task Done. Thanks for your help with this!

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.