Retrieve data from another sheet and add to bottom of destination sheet

SteveE

I am using the formula below to insert a row into a Scorecard sheet to show the {AP Project ID} from the Approved Projects sheet when the {AP Release Cycle} = 2024 and the {AP Project Status} = “Complete”. I created a helper field called row ID.

=IFERROR(INDEX(COLLECT({AP Project ID}, {AP Release Cycle}, “2024”, {AP Project Status}, "Complete"), [Row ID]@row), "")

In addition to the data from the Approved Projects sheet, the Scorecard sheet also has unique comment columns which have been added to the Scorecard sheet, etc (see below).

The issue I am encountering is that when a new project is marked as complete on the Approved Projects sheet the formula retrieves the Project ID from the Approved Projects sheet and inserts it (based on Row ID helper?) into the Scorecard sheet somewhere other than the bottom of the sheet as a new row.

That ends up messing up the Scorecard sheet comments, etc since they do not move when the new data is inserted. In the example below, Project ID ITS0173 was just marked as complete, retrieved from the Approved Projects sheet, and added to row 11 of the Scorecard sheet. And now, the Comment 1 fields from row 11 on, no longer line up.

Is there a different formula I should be using to retrieve the data from Approved Projects sheet and add it to the bottom of the Scorecard sheet as a new row?

Any guidance/help is appreciated.

Thank you very much!!!

Paul Newcome

I would suggest a copy row automation instead.

Paul Newcome

I would suggest a copy row automation instead.

SteveE

Hi Paul,

I thought of that, but was not sure how to make it work since I only need certain columns from the source sheet and I also need "new" columns on the destination sheet. In a row copy wouldn't I get all the columns as they are on the source sheet? Or would it only copy fields that have a match on the destination sheet?

Paul Newcome

It will copy over all columns, but you can hide those to keep them from cluttering things up.

SteveE

I have used row copy once before, but did not completely understand how they work. I thought it would not work with the "additional" columns I had added since there was not a "match" on the source sheet. But I now see, it populates existing columns if there is a matching destination column and adds columns if there is no matching destination. It ignores the "additional" destination sheet columns (which is what I wanted).

Thank you very much Paul!