If 2 cells are blank, insert data from this cell

Simon20447

Hello great minds of Smartsheet!

I have a sheet that is the amalgamation of a few others using datamesh and some forms.

As such I have 3 columns where project name, project number, or task number is listed, depending where it originated from. I would like to make a 4th column called Task Origin which would pull detail from one of the 3 other columns…I would then hide the other 3 columns to tidy it up!

[Task Number] [Project Name] [Project Number] [Task Origin]

Formula wise in the [Task Origin] column, I am looking at…

=IF(ISBLANK(ProjectName@row), [Project Number]@row,

IF(ISBLANK([Project Number]@row), ProjectName@row,

IF(ISBLANK([Project Number]@row:ProjectName@row), [Task Number]@row)))

When i remove the 3rd element of the IF statement, it works as expected. With the 3rd element in, the [Task Origin] Column is blank (no error message). I have tried a few different ways of writing the final ISBLANK section - i am not sure if you can look at multiple cells in an ISBLANK function?? I also tried an IF(AND(ISBLANK…version.

The Project Number column is populated from a Form which allows the person filling in the form to select Projects from a drop down list…could this be the issue - that the cell is not blank, just empty?

Thanks All!

Find more posts tagged with

Accepted answers

All comments

dojones

I'm not exactly sure what you are doing, but this one is specified incorrectly

IF(ISBLANK([Project Number]@row:ProjectName@row), [Task Number]@row)))

You can't do an isblank across columns.

It's not clear to me exactly what you are trying to do.

In words:

If Project Name is blank, return Project Number

If Project Number is blank, return Project Name

What is the last check you are trying to make?

If Project Name and Project is blank, return Task Number

If so, this is the formula

=IF(ISBLANK(ProjectName@row), [Project Number]@row,

IF(ISBLANK([Project Number]@row), [Task Number]@row))

Example:

Simon20447

thank you for your response, I probably haven't described my challenge and aim very clearly…and i think we are not far off…

I have amended your table slightly to try and explain…

Every row has a serial and I'd like this to be in the [Task Origin] column if there is nothing in [Project Number] and [Task Number] - as in Row C

If there is an entry in [Project Number] and [Task Number] they will be the same so it doesn't matter which one ends up in [Task Origin]

There may be entries in just [Project Number] or [Task Number], when this is the case, I'd like the one that isn't blank to be in [Task Origin] - like Row A (101) and Row D (Apples).

Hopefully this makes it a little clearer!

I may be asking for something that isn't possible!

Cheers

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.