Collect/Join-to move cell data to another sheet

Allison Crull

I would like to create a formula that looks at columns in one sheet and depending on the cell value copies the information to another sheet.

If the Technician column has a specific name (insert persons name) and the VAL column has 'Yes' or 'Blank' then move cell data from Technician column , VAL column and TKT # to another sheet.

=JOIN(COLLECT({IT Open Project and Program Tasks / Val}, "Yes" {IT Open Project and Program Tasks / Technician}, Developer1, {IT Open Project and Program Tasks / TKT#}, [ITCR #]@row))

I really have no idea how to make this worked so tried the above formula

Leah Rankin

If I'm understanding correctly what you are trying to do (which I very well may not be), I think you are better off using a workflow than a cell formula. I would try this:

1. Click the Automation tab at the top of your sheet (the source sheet, not the destination sheet), then Create a workflow.
2. For Trigger, change it to "When rows are changed"
3. Next to When, select your Technician field, then in the box that pops up, check the Any Value box
4. Click "Add a condition to filter rows"
5. Next to Where, select your VAL field, then select "Yes" and "Blank" from the box that pops up
6. In the "Select an Action" box, choose "Move rows" (or copy, your choice)
7. Click the "+ Select a Sheet" link and choose the sheet you want to move the row to

That solution will be the best to assign tasks as they come in, for example. I use this type of workflow pretty regularly to assign tasks to different sheets based on what category of request users put in a form. If you want to collect all open tasks by assignment for a particular person just to view, you could also try a report.

Sorry if I'm not understanding correctly what you're trying to do. I can try to help fix your JOIN(COLLECT()) statement if you can help me understand better what you're looking for.

Allison Crull

@Leah Rankin Hi -Leah

That would not work-the sheet do not have the same columns and we only need 3 column cells to move.

Leah Rankin

@Allison Crull

Hey Allison,

Sorry, just saw your response. Super helpful and now I understand better. Sorry for missing that before. I think a JOIN(COLLECT()) is probably still not going to do what you want it to do here. You can collect the information from the other sheet this way, but it will still stay in the other sheet, and it will come into the new sheet as one column for ALL matching rows. For example, if you wanted one cell to show all ticket numbers assigned to a specific tech, this would work super well and you could do something like:

=JOIN(COLLECT({column from other sheet with ticket numbers},{column from other sheet with technician names},"Technician Name", ", ")

But it sounds like you are actually trying to create a series of rows with these three columns in them, right? I totally get how the sheet automation is too constrained to meet your needs, so maybe a report would work instead? I would try a row report, with your original sheet as the source sheet. You can then choose only those three columns to show and can filter it by the technician name. Would that work for you? I'm trying to think of a better way, but this is all I am coming up with at the moment. If that won't do it for you either, I'll keep thinking on it or maybe someone else will have a great idea.

Andrée Starå

Hi @Allison Crull

I hope you're well and safe!

To add to Leah's excellent advice/answer.

If I understand you correctly, you could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

Would that work/help?

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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!