I want to copy row information automatically based on if a certain colomn is filled in

Tracy PIneda

But I don't want to take the WHOLE row, I just want a couple columns. Is there a formula? I know there is an automation workflow, but that moves the whole row (all the columns). I just want 4 columns to be copied to this other sheet.

Danielle Arteaga

My approach would be this:

On the YMHFA Contact List sheet, I would manually type the Client ID value in that cell for each row.

In the Participant column, set a column-level formula as follows:

=IFERROR(INDEX({Vuela Intake Form 2020 Participant}, MATCH([Client ID]@row, {Vuela Intake Form 2020 Client ID}, 0)), "")

In the Phone Number column, set this column-level formula:

=IERROR(INDEX({Vuela Intake Form 2020 Phone}, MATCH([Client ID]@row, {Vuela Intake Form 2020 Client ID}, 0)), "")

In the YMFHA Cohort column, set this column-level formula:

=IFERROR(INDEX({Vuela Intake Form 2020 Cohort}, MATCH([Client ID]@row, {Vuela Intake Form 2020 Client ID}, 0)), "")

Couple of things to note:

1. The references in {} are cross-sheet references you will have to create - you can't just type them in the first time. You can pick whatever identifying names you want for them - I just tried to use your descriptions for clarity.
2. Column-level formulas a pretty new (yay!) and are set by typing the formula as you typically would in a cell, then right-clicking the cell and selecting Convert to Column Formula. This allows your formula to automate down the column - even as you add new rows.
3. The IFERROR wrapper around each formula will return a blank (rather than #NO MATCH) value if you do not have a Client ID assigned on your main sheet.

Andrée Starå

Hi @Tracy PIneda

You can either copy the row and hide the columns you don’t want to show or use cross-sheet formulas VLOOKUP or INDEX/MATCH to only show the columns you’d like in the other sheet.

Make sense?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Tracy PIneda

@Andrée Starå

It makes sense but it needs to be automated, so that when a new row gets added to the first sheet 4 columns of the original sheet get placed in the destination sheet. The original sheet has 30+ coloumns.... I am going to try the formulas that you mentioned.

Andrée Starå

@Tracy PIneda

Happy to help!

The simplest method would be to add either an auto-number column and a so-called helper column in the Destination sheet where you add the numbers manually, and then you'd use that for the cross-sheet formula. (if you don't have any other unique values, we can use instead)

Make sense?

Tracy PIneda

Here is a pic of the original sheet; I want to transfer the Date, Client ID, Last Name, First Name, Participant and YMHFA Cohort to the follwing sheet. Client ID is an Auto number column. What would that formula look like? What is the helper column? Some days I feel so good at this, others I feel like I don't know anything

😕

Here is the destination sheet I want to autofill the Client ID, participant, phone number and YMHFA Cohort.

Thanks in advance for your help!

Tracy

Danielle Arteaga

My approach would be this:

On the YMHFA Contact List sheet, I would manually type the Client ID value in that cell for each row.

In the Participant column, set a column-level formula as follows:

=IFERROR(INDEX({Vuela Intake Form 2020 Participant}, MATCH([Client ID]@row, {Vuela Intake Form 2020 Client ID}, 0)), "")

In the Phone Number column, set this column-level formula:

=IERROR(INDEX({Vuela Intake Form 2020 Phone}, MATCH([Client ID]@row, {Vuela Intake Form 2020 Client ID}, 0)), "")

In the YMFHA Cohort column, set this column-level formula:

=IFERROR(INDEX({Vuela Intake Form 2020 Cohort}, MATCH([Client ID]@row, {Vuela Intake Form 2020 Client ID}, 0)), "")

Couple of things to note:

1. The references in {} are cross-sheet references you will have to create - you can't just type them in the first time. You can pick whatever identifying names you want for them - I just tried to use your descriptions for clarity.
2. Column-level formulas a pretty new (yay!) and are set by typing the formula as you typically would in a cell, then right-clicking the cell and selecting Convert to Column Formula. This allows your formula to automate down the column - even as you add new rows.
3. The IFERROR wrapper around each formula will return a blank (rather than #NO MATCH) value if you do not have a Client ID assigned on your main sheet.

Andrée Starå

@Tracy PIneda

I saw that Danielle answered already!

Let me know if I can help with anything else!

Best,

Andrée

Tracy PIneda

@Danielle Arteaga thanks a bunch, so helpful. I am going to try that out in a few!

Tracy