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", ""))

Best Answers

  • Erin Horiuchi Green
    Erin Horiuchi Green ✭✭✭✭✭
    Answer ✓

    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, MBA, LSSYB, PSMI
    Process Manager
    Syneos Health

    Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.

    Core App and Project Managment Certified 🚀

  • Erin Horiuchi Green
    Erin Horiuchi Green ✭✭✭✭✭
    Answer ✓

    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.

    Erin Horiuchi Green, MBA, LSSYB, PSMI
    Process Manager
    Syneos Health

    Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.

    Core App and Project Managment Certified 🚀

Answers

  • Erin Horiuchi Green
    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.

    Erin Horiuchi Green, MBA, LSSYB, PSMI
    Process Manager
    Syneos Health

    Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.

    Core App and Project Managment Certified 🚀

  • 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.

  • Erin,

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

  • Erin Horiuchi Green
    Erin Horiuchi Green ✭✭✭✭✭
    Answer ✓

    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, MBA, LSSYB, PSMI
    Process Manager
    Syneos Health

    Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.

    Core App and Project Managment Certified 🚀

  • 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
    Erin Horiuchi Green ✭✭✭✭✭

    Hello @Dustin Jenkins ,

    I just sent the meeting invitation now.

    Erin Horiuchi Green, MBA, LSSYB, PSMI
    Process Manager
    Syneos Health

    Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.

    Core App and Project Managment Certified 🚀

  • Erin Horiuchi Green
    Erin Horiuchi Green ✭✭✭✭✭
    Answer ✓

    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.

    Erin Horiuchi Green, MBA, LSSYB, PSMI
    Process Manager
    Syneos Health

    Please kindly like ❤️, upvote ⬆️ and/or mark ✅ any of my contributions that have provided value.

    Core App and Project Managment Certified 🚀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!