Need help with an INDEX(COLLECT formula
Hello,
This is my formula =INDEX(COLLECT({103 Copy Total January Hours}, {103 Copy Project Title}, @cell = [Project Title]@row), COUNTIFS({103 Copy Project Title 2}, @cell = [Project Title]@row) - 1). It worked fine until I changed Project ID to Project Title, and I cannot get it to work, and I have tried everything. I have a source sheet and a copy sheet. The formula is on the source sheet, and it is supposed to be looking for numbers on the copy sheet.
The formula sort of worked earlier, but every line had 60 as the Previous January Estimated Hours
Please tell me what is wrong.
Thanks,
Ashley
Best Answers
-
I believe the issue lies with the Sheet name reference. If the reference is on the same sheet and column, it should have the same name.
Hope this helps!
che
-
Hi @Ashley McAdoo,
please try the following formula:
=INDEX(COLLECT({103 Copy Total January Hours}, {103 Copy Project Title}, @cell = [Project Title]@row), 1)
This will give you the first matching "Total January Hours" for the "Project Title" in the current row.
To specifically address getting the last occurrence in Smartsheet, you would typically need a workaround that involves additional steps or columns, such as:
- A column to track the occurrence number of each "Project Title" in your copy sheet.
- Use this occurrence number to filter down to the last occurrence in your formula by using a combination of MAX and COLLECT.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
I believe the issue lies with the Sheet name reference. If the reference is on the same sheet and column, it should have the same name.
Hope this helps!
che
-
Hi @Ashley McAdoo,
please try the following formula:
=INDEX(COLLECT({103 Copy Total January Hours}, {103 Copy Project Title}, @cell = [Project Title]@row), 1)
This will give you the first matching "Total January Hours" for the "Project Title" in the current row.
To specifically address getting the last occurrence in Smartsheet, you would typically need a workaround that involves additional steps or columns, such as:
- A column to track the occurrence number of each "Project Title" in your copy sheet.
- Use this occurrence number to filter down to the last occurrence in your formula by using a combination of MAX and COLLECT.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!