Moving latest entry into a new sheet

Chitta

Hi,

Would be great help if someone can suggest a solution to my below queries.

Here is my scenario: I am collecting many column data through forms. Once in 2 weeks I collect status from project team so that I can share latest update to my leadership team.

Queries:

1. Is it possible to fill in already available row data into forms and share the forms project teams to update only required info?
2. If possible, there will be a duplicate entry in my sheet as I believe there is no option to update the sheet. so I want to move the project wise latest row into a new sheet so that updated row show in my dashboard. For example, I have Task_Name as project name and created date (latest date) this might be combination for my formula. I want to move only new entry new sheet.

Thanks in advance and hope my question are clear, if you can suggest any other approach would be really helpful.

Thanks, ~Chitta

Paul Newcome

Have you looked into sending an Update request instead of a form creating a new row?

James Keuning

Here is how I do it. I have a sheet which is a list of my projects (each project appears once). The I have another sheet that has a list of project updates. Obviously, this sheet has each project repeated many times, one record for each update. This solution uses COLLECT, LARGE, MATCH, and INDEX. It uses LARGE because I have the option to retrieve that last three updates, rather than only the latest.

So in my list sheet I have the Project Name and then this formula:

=INDEX({IndexRange}, (MATCH((LARGE(COLLECT({Updates1}, {Updates2}, [Primary Column]@row), 1)), {Updates1}, 0)), 5)

That formula looks at the updates Sheet:

IndexRange - range in the lookup sheet, basically an autonumber and an update narrative (along with some other information fields, hence the column_index on column 5).

Updates1 - the autonumber column in the lookup sheet

Updates2 - the project name field in the lookup sheet

What the formula does is collect all of the autonumbers for updates that match the Project name. Then identifies the largest autonumber (the most recent update), identifies the record/row number for that data, and then grabs the update narrative (column 5) for the record/row.

You could use a date field for Updates1 range, which I might have done if I was to do it again. But there are tradeffs.

Chitta

Hi James,

Thank you so much, very well explained.

I tried using your logic and I am using below formula but facing "UNPARSEABLE" error, something I am doing wrong here, kindly help.

=INDEX([EndUsers_Launch]1:[EndUsers_Launch]8, (MATCH((MAX(COLLECT([EndUsers_Launch]@row, [ProjPlan_TaskName]@row),1)),[EndUsers_Launch]1),0))5)

Here is my table:

Do you suggest I should use a autonumber column in this scenario? as I wanted to fetch only latest data identified in from date column.

Can you also explain the approach you are using to take last 3 updated rows into new sheet? Are you using automation that every time there is a new row inserted to trigger the automation into new sheet?

Thanks in advance,

Chitta

Paul Newcome

Have you looked into sending an Update request instead of a form creating a new row?

Chitta

Thank you, Paul. I am exploring your suggestion, will get back to you if this works for me.