INDEX/COLLECT to add Error Statement if no TDL #
![SherryFox](https://us.v-cdn.net/6031209/uploads/userpics/LHA9XZ0T0H53/nMRKE1FQSRA0S.jpg)
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.
Best 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?
Answers
-
@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.
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!