I want to copy row information automatically based on if a certain colomn is filled in
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.
Best Answer
-
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:
- 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.
- 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.
- 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.
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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:
- 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.
- 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.
- 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.
-
I saw that Danielle answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Danielle Arteaga thanks a bunch, so helpful. I am going to try that out in a few!
Tracy
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives