Need help with an INDEX(COLLECT formula

Ashley McAdoo

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

Formula won't work.png

Please tell me what is wrong.

Thanks,

Ashley

che.rabajante

Hi @Ashley McAdoo

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

Bassam Khalil

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.

che.rabajante

Hi @Ashley McAdoo

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

Bassam Khalil

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.