Return information from a cell from another sheet, by using a ref no. created by an autonumber colum

A.Turner

I have three sheets:

1. Partner sheet, with different projects that have a project number allocated to them via an autonumber column.
2. Action sheet, when an action is added to the partner sheet for a particular project, it automatically duplicates the project number and action onto the action sheet.
3. Completed Action sheet, when an action has been completed on the Action sheet, a 'resolution' is added to project number line, a completed box is ticked, and it then automatically moves to the Completed Action sheet with the project number and the resolution. When it moves into the Completed Action sheet, a "completion' date is created.

What I want to do:

I want the most recently updated 'resolution' to show from the Completed Action sheet in a dedicated cell in the Partner sheet according to it's project number.

What I have tried (AMONGST MANY OTHERS):

formula 1 =IFERROR(INDEX({COMPLETED ACTIONS RESOLUTION}, MATCH(MAX(COLLECT({COMPLETED ACTIONS COMPLETION DATE}, {COMPLETED ACTIONS PROJECT NUMBER} + "", =[PROJECT Number]@row + "")), COLLECT({COMPLETED ACTIONS COMPLETION DATE}, {COMPLETED ACTIONS PROJECT NUMBER} + "", =[PROJECT Number]@row + ""), 0)), " ")

formula 2 =IFERROR(INDEX({Completed Actions RESOLUTION},
MATCH(MAX(COLLECT({Completed Actions COMPLETION DATE},
VALUE({Completed Actions PROJECT NUMBER}),
= VALUE([Project Number]@row))),
COLLECT({Completed Actions COMPLETION DATE,
VALUE({Completed Actions PROJECT NUMBER}),
= VALUE([Project Number]@row)),
0)),
" ")

Problems:

• autonumber column
• formula 1 returns nothing
• formula 2 returns a resolution but it is for the project number and not even for the latest date of anything on the sheet

I had previously used this formula:

=IFERROR(VLOOKUP([Project Number]@row, {COMPLETED ACTIONS RESOLUTION}, 18, false), " ")

This formula brought through a resolution for the correct project number, but not the most recent entry by the completed date for that project.

Here is a pic of the Completed Action sheet:

I want to latest comment from the 6/2 in the Concierge Final Resolution column to show in the partner sheet for project/action number 000000312 (Project 1)

If anyone could help me I would be soooooo grateful, I have spent far too long on trying to get this already!!! I am desperate!!!!

A.Turner

@Paul Newcome I finally figured it out! Thanks for trying to help me, I appreciate it!

This is the final formula:

=IFERROR(INDEX(COLLECT({COMPLETED ACTIONS Partners & CHPs Range 2}, {COMPLETED ACTIONS Partners & CHPs Range 3}, MAX(COLLECT({COMPLETED ACTIONS Partners & CHPs Range 3}, {COMPLETED ACTIONS Partners & CHPs Range 5}, [Helper Column]@row)), {COMPLETED ACTIONS Partners & CHPs Range 5}, [Helper Column]@row), 1), "")

Paul Newcome

You would use an INDEX/COLLECT along the lines of

=INDEX(COLLECT({Resolution}, {Developer}, @cell = [Project Number]@row), COUNTIFS({Developer}, @cell = [Project Number]@row))

A.Turner

I tried to edit the above but couldn't. Can you please take a look at below:

I have three sheets:

1. Partner sheet, with different projects that have a project number allocated to them via an autonumber column.
2. Action sheet, when an action is added to the partner sheet for a particular project, it automatically duplicates the project number and action onto the action sheet.
3. Completed Action sheet, when an action has been completed on the Action sheet, a 'resolution' is added to project number line, a completed box is ticked, and it then automatically moves to the Completed Action sheet with the project number and the resolution. When it moves into the Completed Action sheet, a "completion' date is created.

What I want to do:

I want the most recently updated 'resolution' to show from the Completed Action sheet in a dedicated cell in the Partner sheet according to it's project number.

What I have tried (AMONGST MANY OTHERS):

formula 1 =IFERROR(INDEX({COMPLETED ACTIONS RESOLUTION}, MATCH(MAX(COLLECT({COMPLETED ACTIONS COMPLETION DATE}, {COMPLETED ACTIONS PROJECT NUMBER} + "", =[PROJECT Number]@row + "")), COLLECT({COMPLETED ACTIONS COMPLETION DATE}, {COMPLETED ACTIONS PROJECT NUMBER} + "", =[PROJECT Number]@row + ""), 0)), " ")

formula 2 =IFERROR(INDEX({Completed Actions RESOLUTION}, MATCH(MAX(COLLECT({Completed Actions COMPLETION DATE}, VALUE({Completed Actions PROJECT NUMBER}), = VALUE([Project Number]@row))), COLLECT({Completed Actions COMPLETION DATE, VALUE({Completed Actions PROJECT NUMBER}), = VALUE([Project Number]@row)), 0)), " ")

formula 3 =IFERROR(INDEX({COMPLETED ACTIONS resolution}, MATCH(LARGE(COLLECT({COMPLETED ACTIONS completed date}, {COMPLETED ACTIONS helper column}, =[Helper Column]@row), 1), {COMPLETED ACTIONS completed date}, 0)), " ")

This formula number 3 has been the closest so far. And the results are in the example picture I have added further below.

I had previously used this formula:

=IFERROR(VLOOKUP([Project Number]@row, {COMPLETED ACTIONS RESOLUTION}, 18, false), " ")

This formula brought through a resolution for the correct project number, but not the most recent entry by the completed date for that project.

Here is a pic of the Completed Action sheet:

And here is a pic of the partner sheet where I want to pull the resolution information into:

This formula is what is used in the picture:

formula 3 =IFERROR(INDEX({COMPLETED ACTIONS resolution}, MATCH(LARGE(COLLECT({COMPLETED ACTIONS completed date}, {COMPLETED ACTIONS helper column}, =[Helper Column]@row), 1), {COMPLETED ACTIONS completed date}, 0)), " ")

NOTE: I added in a helper column due to the auto-number thing and made the the project number (action number in the sheet) a number (I hope).

I have realised the problem is that the formula is not working when there are two different project resolutions with the same completion date. As you can see project project 317 and 313 have the same completion date, so 317 is pulling through the information for 313.

I don't know what to fix in the formula, chatgpt and I have been conversing and troubleshooting this all day!

If anyone could help me I would be soooooo grateful, I have spent far too long on trying to get this already!!! I am desperate!!!!

A.Turner

@Paul Newcome I forgot to tag you in above, could you please take a look?

A.Turner

@Paul Newcome I tried your formula and I got #invalid column value unfortunately

Paul Newcome

What is the exact formula you used that threw the invalid column value error, and what are the column properties for the column you are putting the formula in? Also double check the source sheet in the columns referenced to ensure that particular error is not present there either.

A.Turner

@Paul Newcome I tried your formula again and it kind of worked:

=INDEX(COLLECT({COMPLETED ACTIONS Partners & CHPs Range 2}, {COMPLETED ACTIONS Partners & CHPs Range 5}, @cell = [Helper Column]@row), COUNTIFS({COMPLETED ACTIONS Partners & CHPs Range 5}, @cell = [Helper Column]@row))

This is what it came out like on the partner sheet:

Completed actions sheet where the information comes from:

This seems to have fixed the issue of the duplicate dates.

However, if I change the date of the most recent resolution for helper column project number 314 so it is no longer the most recent resolution anymore, Smartsheet does not recognise this and continues to bring through the same resolution:

Here are the date changes and I adjusted the resolution comments:

And here is what it showing on the project sheet (bottom row):

I am unsure of how your formula pulls the most recent resolution, as I did not reference the "Date Completed" column in it?

Here are the formats of the columns:

Partner Sheet

Action Number = Auto Number

Helper Column = Text/Number + fx

Developer/Proponent = Text/Number

Concierge Final Resolution = Text/Number, fx (where the formula sits that I am trying to do)

Completed Actions Sheet

note: All of the columns are automatically moved onto this sheet from the Actions Log, which is duplicated from the project sheet.

Action Number = Auto Number

Helper Column = Text/Number

Developer/Proponent = Text/Number

Concierge Final Resolution = Text/Number

The other formula I used that worked but had the trouble with 2 different project numbers having the same dates was:

=IFERROR(INDEX({COMPLETED ACTIONS Partners & CHPs Range 2}, MATCH(LARGE(COLLECT({COMPLETED ACTIONS Partners & CHPs Range 3}, {COMPLETED ACTIONS Partners & CHPs Range 5}, =[Helper Column]@row), 1), {COMPLETED ACTIONS Partners & CHPs Range 3}, 0)), " ")

I appreciate your help with this. I have been going at this for 2 days now and it's driving me crazy!

A.Turner

@Paul Newcome I finally figured it out! Thanks for trying to help me, I appreciate it!

This is the final formula:

=IFERROR(INDEX(COLLECT({COMPLETED ACTIONS Partners & CHPs Range 2}, {COMPLETED ACTIONS Partners & CHPs Range 3}, MAX(COLLECT({COMPLETED ACTIONS Partners & CHPs Range 3}, {COMPLETED ACTIONS Partners & CHPs Range 5}, [Helper Column]@row)), {COMPLETED ACTIONS Partners & CHPs Range 5}, [Helper Column]@row), 1), "")

Paul Newcome

My solution was based on the assumption nothing would be changed on the reference sheet. That the reference sheet was just static data populated by a copy row automation which always puts the most recent row at the bottom and wouldn't have dates changed (and therefor wouldn't need to account for dates, just pull in the last entry for that project identifier).