I need to create a subset of the data on one sheet that can be actioned and then linked back.
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?
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
Answers
-
Why will a report not work?
-
That is the directive from management. I believe they want to create automation from this separate sheet.
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
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), "")
-
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?
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
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.
-
Gotcha. I'll present best practice options at our next working session and move from there.
As always, thanks @Paul Newcome
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!