Copy a range of cells under a parent or ID number to another sheet

David MacDonald

We have a wide range of purchase orders which can be created for any job from 1-50. There are about 6 different PO groups I would like to pull data from and assign them to their individual projects in their corresponding rows. Is there a function where I can call the parent and it will copy the parent and children rows/specific cells into another sheet?

Each purchaser will have a master list to track all their orders.

I would like to have this to keep the function dynamic as POs maybe be added as the project continues. This would speed up seek time to end constantly checking each POs master order sheet and scrolling to copy and paste each PO group.

I am assuming all my column headers would need to match? Or can I select specific cells in each row as well in the dynamic group.

Can index pull data across sheets like this?

Thanks!

Andrée Starå

Hi @David MacDonald

I hope you're well and safe!

• If I understand your need correctly. You could have something trigger the copy of the parent and children if it's time-based, but it will always be added to the end of the page.
• Another option would be to use cross-sheet formulas to collect the data, but then you would have to create the hierarchy manually if it's not already created.

Make sense?

Would any of those options 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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

David MacDonald

I am looking to use cross sheet formulas to copy the information to a specific project. I already have the hierarchy setup with matching columns. If I have to leave designated spaces for each PO category is no problem. What formula would accomplish this?

Andrée Starå

@David MacDonald

You'd use/I recommend INDEX/MATCH.

The structure would look something like this.

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row, {ColumnWithTheValueToMatchAgainsTheCell}, 0))

More info.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Make sense?

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

David MacDonald

That's what I have been using but it only pulls a single row. It is possible to pull multiple rows?

For example I want to pull all the POs in this sheet with one job number. I was trying to to do it with a helper column with no luck.

Pull from here:

I understand if the receiving sheet needs to have assigned rows to receive the info which is not a problem. I just want to have the function be able to pull all rows with the matching job number to the correct project.

Paste in here:

I would appreciate any direction.

David MacDonald

Would INDEX(COLLECT) be able to accomplish this or is that column specific instead of the row?

Genevieve P.

Hi @David MacDonald

Formulas will bring in one row at a time; you could use INDEX(COLLECT and then specify at the end of each formula what row number to bring back:

INDEX(COLLECT(formula), 1)

then for the second matching row:

INDEX(COLLECT(formula), 2)

and the third...

INDEX(COLLECT(formula), 3)

And so on. However to do this you would need to know how many child rows are associated with each Parent Job Number. It would also require you to add the Job Number into each of the cells of the Child Row, so the COLLECT part of the formula can filter down what rows to bring back.

I've seen other members use a helper column to identify the number of rows, so each cell has a number and then you could reference that column instead:

INDEX(COLLECT(formula), [Row Number]@row)

But again, it wouldn't know when you need the formula to stop (how many rows are needed to be returned).

An alternative would be to manually use the "Copy Row" feature which would copy the hierarchy and all of the child rows to another sheet, then move them to the correct position, see: Copy rows to another sheet

Cheers,

Genevieve

David MacDonald

Formula Help.pdf

Hi Genevieve,

As I read your comment, I fully understand what you are telling but for the life of me I am having difficulty with the execution. I know how to index(match) but collect I seem to always end up with an incorrect argument.

I've attached the workflow for the formula that I want to use. Can you let me know what I'm doing wrong with my equation?

David MacDonald

Formula Help 2.pdf

Hi Genevieve,

I've finally have it working! I will try to have it fill out multiple rows and apply and if error to hide incomplete rows.