In work Formula

Hello,

I'm trying to figure our this formula and need some help. I'm not sure if someone on this discussion board would be willing to have a meeting but that would be even better to help me. I want the formula to be; If this other referenced sheet has this number, in this column and this cell of this row in this column has this word. If i select the specific cell for the other sheet it works but if i select the whole column to search it does not work. I want it to pull the data no matter what cell the number is put in and word cell so i don't have to manual select that cell each time. Automation to report to other sheets essentially. See examples below and thank you for your help.

Specific Cell Formula that works

=IF(AND({V-22 Mod WIP Range 6} = 168675, HAS({V-22 Mod WIP Range 7}, "TCAS A2")), "In Work", "")

Column Formula that does not work and meets my intent

=IF(AND({V-22 Mod WIP Range 1} = 168675, {V-22 Mod WIP Range 2} = "LSPR A2C", "In Work", ""))

Find more posts tagged with

Accepted answers

Erin Horiuchi Green

Hello @Dustin Jenkins ,

I am available today from 1:30 - 3:00 pm ET.

Let me know and I will send you an invite to dustin.w.jenkins@boeing.com.

Erin Horiuchi Green

Hello @Dustin Jenkins

Glad I could help you with the below and enhance with the Status column in the underlying source sheet.

=IF(AND(HAS({V-22 Mod WIP Range 5}, "Inducted"), HAS({V-22 Mod WIP Range 1}, "168675"), HAS({V-22 Mod WIP Range 4}, "TCAS A2")), "In Work", "")

I am thinking that you can bring in the 2 columns with indicator symbols using Join embedded with Collect. You may have to add the column in the aggregated sheet and hide it.

All comments

Erin Horiuchi Green

Hello @Dustin Jenkins,

I would need to meet to discuss what is the big picture and current framework. Copy with dummy data is fine or screenshots with blacked outs.

I have found that I have had to interlace DataTable, Data Shuttle, Pivot and DataMesh to configure my solutions. Please provide me some availability options in US EST time zone.

Also, I have had to accept the limitations of Smartsheet in general and submit enhancement requests.

Dustin Jenkins

Erin,

I am PST how does 9 AM PST/12 PM EST work, 9:30 AM PST/12:30 EST, 10:30 AM PST/1:30 PM EST, 11 AM PST/2 PM EST. A few options for you and not sure if you can do later past. Also here is my email dustin.w.jenkins@boeing.com for simpler communication.

Dustin Jenkins

Erin,

Checking back in to see if you have some availability to help me with my formula?

Erin Horiuchi Green

Hello @Dustin Jenkins ,

I am available today from 1:30 - 3:00 pm ET.

Let me know and I will send you an invite to dustin.w.jenkins@boeing.com.

Dustin Jenkins

Erin,

That works for me so lets do 11 am PST/ 2 PM EST. Thank you so much. Ill be on the look out for the meeting invite and that is my correct email.

Erin Horiuchi Green

Hello @Dustin Jenkins ,

I just sent the meeting invitation now.

Erin Horiuchi Green

Hello @Dustin Jenkins

Glad I could help you with the below and enhance with the Status column in the underlying source sheet.

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