I need to create a subset of the data on one sheet that can be actioned and then linked back.

Darla Brown

Hi,

I have one sheet that is a listing of all commissionable groups. The team processes the commissions from this sheet. Of those, there is a subset of agencies that we prepay 50%. We want to create a separate actionable sheet that pulls the data from the commissionable group sheet for only those we prepay. Once those payments are made, that data would be linked to the commissionable groups sheet.

At first, I thought I could use DataMesh, but I can't filter the data. Would the best practice be to just create 2 sheets with the same information shuttling in but filter the agencies for prepaid? I don't want to create a report. It needs to be 2 sheets.

Thoughts?

Paul Newcome

Why will a report not work?

Darla Brown

That is the directive from management. I believe they want to create automation from this separate sheet.

Paul Newcome

Automations can be pushed from the source sheet too though.

But if they are truly insistent on a second sheet, you will need a helper column on the destination sheet that has numbers manually entered in it starting with 1 and going down as far as you will need (plus a buffer just in case) to accommodate all of the rows that need to be pulled in. From there you would use an INDEX/COLLECT formula and reference this helper column.

=IFERROR(INDEX(COLLECT({Column To Pull From}, {Prepay Column}, "prepay value"), [Number Helper]@row), "")

Darla Brown

I plan to present all scenarios to them and offer recommendations but currently, they are set on 2 sheets.

For clarification, I would be using the helper column as reference to run DataMesh?

Paul Newcome

I missed the DataMesh part. So here are three options in the order that I personally would suggest to my own clients:

1) Create automations on the source sheet and use a report to display if needed.

2) Helper column on source sheet and matching helper column on destination sheet to provide a unique ID for DataMesh.

3) Helper column on destination sheet to allow for the INDEX/COLLECT solution above.

Darla Brown

Gotcha. I'll present best practice options at our next working session and move from there.

As always, thanks @Paul Newcome

Paul Newcome

Happy to help. 👍️