Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

INDEX/COLLECT to add Error Statement if no TDL #

Hello, and thanks in advance for your help on my formula. I do not understand why I am getting this error on my formula. I am working on adding my formula to the sheet in the first screenshot. I am referencing the sheet in the second screenshot. The 1 and 2 reference which part is which for the named ranges. I know why I am getting this issue (sort of).I discovered In this case that the TDL # (far left columns) does not exist on the sheet. Now in this instance, I would like to return "No TDL #", and obviously if the TDL does exist, then the result should display like it is. But I am not sure how to convert this formula.

=INDEX(COLLECT({1.0 TDL-ProjectSites_MP}, {1.0 TDL-ProjectSites_TDL}, [TDL #]@row), 1)

Sherry Fox

Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies

EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

Core App Certified 🦊

NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

Connect with me on LinkedIn

Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.

Tags:

Best Answer

Answers

  • Community Champion

    @SherryFox Give this a try. I didn't test it, but I believe it should work.

    =IF(COUNTIFS({1.0 TDL-ProjectSites_TDL}, [TDL #]@row) > 0, INDEX(COLLECT({1.0 TDL-ProjectSites_MP}, {1.0 TDL-ProjectSites_TDL}, [TDL #]@row), 1), "No TDL #")

  • ✭✭✭✭✭

    Hello @SherryFox

    You can also use the IFERROR function.

    Try this:

    =IFERROR(INDEX(COLLECT({1.0 TDL-ProjectSites_MP}, {1.0 TDL-ProjectSites_TDL}, [TDL #]@row), 1),"No TDL #")

    Melissa Yamada
    melissa@insightfulsheets.com
    Data made simple, spreadsheets reimagined

  • ✭✭✭✭

    @Carson Penticuff and @Melissa Yamada ,

    I am getting errors (Unparsable with both your formulas). These formulas will be converted to column formulas if that matters. Since I can not edit my original post, I took revised screenshots. The first shows the sheet where the formula will live, in the Mission Partner cell. It will access the TDL Number cell as a lookup reference point. It will then use that lookup on the 2nd sheet (1.0 TDL-PrpjectSites) to find the data. The TDL # can be found in the column called Title (the named range is in red), and the results array is in the MissionPartner column (again with the named range for the results array in red). Perhaps this may help clear it up.

    Sherry Fox

    Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

    Core App Certified 🦊

    NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

    Connect with me on LinkedIn

    Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.

  • Community Champion
    Answer ✓

    From your screenshot, I see [TDL Number] as the column name. If you substitute this for [TDL #] in either of the formulas above, do you still receive #unparseable?

  • ✭✭✭✭

    @Carson Penticuff

    I did not even notice that difference!!! That was exactly what I needed in order to make the correction to my account. Thanks so very much!

    Sherry Fox

    Business Process Analyst 3 | C5ISR Group | HII | Mission Technologies

    EAP | Mobilizer | Automagician | Superstar | Community Champion Class of 2024

    Core App Certified 🦊

    NEW (Work) Profile: @SherryFox | OLD (Personal) Profile: @Sherry Fox

    Connect with me on LinkedIn

    Please Like ❤️, Vote Up ⬆️, Awesome 🤩, Insightful 🧐 any of my contributions that have provided value.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions