Weird Formula Help

Nicole J

I have the following columns in my sheet:

Is This An Object? (a drop down "Yes" or "No" column)
Object #
Box #
Folder #
HW ID

I need a formula for the HW ID column that mirrors the "Object #" IF the "Is This An Object?" cell is "Yes".

However, IF the "Is This An Object?" cell is "No", then I want the HW ID column to display "Box #" PLUS "Folder #".

Thoughts?

Find more posts tagged with

Formulas

Accepted answers

Nic Larsen

Something like this:

=IF([Is This An Object]@row = "Yes", [Object #]@row, [Box #]@row + "" + [Folder #]@row)

Can add a space or a dash if needed between the quotes or take that out depending on needs.

Danielle Arteaga

Assuming your sheet set up looks something like this:

Screenshot 2024-09-10 at 12.59.55 PM.png

Put this formula in your HW ID column:

=IF([Is this an Object]@row = "Yes", [Object #]@row, JOIN([Box #]@row:[Folder #]@row))

Note that this works only if [Box #] and [Folder #] are adjacent columns. If they are not, then you'd need a slightly different syntax:

=IF([Is this an Object]@row = "Yes", [Object #]@row, ([Box #]@row + [Folder #]@row))

If you want a delimiter between [Box #] and [Folder #], you can do it like this:

=IF([Is this an Object]@row = "Yes", [Object #]@row, ([Box #]@row + "-" + [Folder #]@row))

So your results look like this:

All comments

Nic Larsen

Something like this:

=IF([Is This An Object]@row = "Yes", [Object #]@row, [Box #]@row + "" + [Folder #]@row)

Can add a space or a dash if needed between the quotes or take that out depending on needs.

Danielle Arteaga

Assuming your sheet set up looks something like this:

Put this formula in your HW ID column:

=IF([Is this an Object]@row = "Yes", [Object #]@row, JOIN([Box #]@row:[Folder #]@row))

Note that this works only if [Box #] and [Folder #] are adjacent columns. If they are not, then you'd need a slightly different syntax:

=IF([Is this an Object]@row = "Yes", [Object #]@row, ([Box #]@row + [Folder #]@row))

If you want a delimiter between [Box #] and [Folder #], you can do it like this:

=IF([Is this an Object]@row = "Yes", [Object #]@row, ([Box #]@row + "-" + [Folder #]@row))

So your results look like this:

Nicole J

Thank you, Nic and Danielle. Both of your responses were correct, helpful, and appreciated. Problem solved.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.