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.
Using CONTAINS in INDEX/COLLECT cross-sheet formula
Can I use the CONTAINS function within an INDEX/COLLECT cross-sheet formula? I need to pull the Finish dates from a schedule for Tasks that contain a designated text string. There will be multiple tasks that contain that text string. Here is the formula that I have used so far, but I am getting an #INCORRECT ARGUMENT SET error right now. I appreciate any assistance!
Best Answer
-
Try this instead:
=INDEX(COLLECT({Finish}, {Task Name}, CONTAINS("Text String", @cell), {Level}, 1), 1)
Answers
-
Are you able to provide a screenshot and the formula for context?
-
@Paul Newcome Thanks for responding to my question. I intended to include the formula in my original post. Here it is: =INDEX(COLLECT({Finish}, CONTAINS("Text String", {Task Name}), {Level}, 1), 1). Here are definitions of some of the components of the formula:
- Finish: cross-sheet reference to Finish dates on a very long project schedule that contains repeating tasks for seven levels.
- Task Name: obviously, this is the Task Name column in the project schedule. The Milestones that I am trying to track are not an exact match with the Task Name--they are contained within but not the full Task Name (therefore, the CONTAINS function).
- Level: designation of what level (1-7) within the project schedule a task is.
Sending a screenshot won't really work, as this formula is part of a much bigger solution. It is part of a Metrics sheet that I am attempting to provide my client with the earliest Finish date and the latest Finish date across all levels.
It seems like it is a syntax question that I need to resolve and apply to multiple occurrences. I appreciate your input.
-
Try this instead:
=INDEX(COLLECT({Finish}, {Task Name}, CONTAINS("Text String", @cell), {Level}, 1), 1)
-
@Paul Newcome Thank you so much! That is exactly what I needed! I need to bone up on the @cell function. That seems to have been the missing piece here.
By the way, I will be in Seattle for ENGAGE and the Partner Summit. I hope to meet you while I am there. You are my most reliable resource here on the Community. I don't ask questions very often, but when I do, you seem to be the one I can count on to come through. Thanks again!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
