Hi you wonderful nerds, long time lurker, first time poster.
Scenario: Gym churn risk report. A list of Members who's 6 month contract is coming up for renewal in the next 30 days. The raw data is imported into a sheet from a CRM, column a to g, calculations to see who fits criteria are done column h to k.
A pivot table then picks out those who fit the criteria. Vlookup is is then used to populate a seperate sheet with their contact details, last payment, membership duration etc.
The trainer/gym owner then has some manual column to fill out eg. Contacted, date of contact and notes.
So the left part of the new sheet is dynamic (eg, 2, members are on the sheet, member 1 renews and drops of the list, member 2 place in sheet changes OR member 3 enters the criteria and gets placed in between member 1 and 2 --> member 2 position changes in list).
Challenge: My challenge is how to make the notes from the trainer stick to the original record (eg if it moves position --> notes need to move position or dissapears --> notes are erased). Uploading the notes into the CRM is unfortunately not possible.
UPDATE: I found a solution on how to do this in excel (self referencing tables) via Power Query, however have not found an example for how to do it in Sheets.
Scenario: Gym churn risk report. A list of Members who's 6 month contract is coming up for renewal in the next 30 days. The raw data is imported into a sheet from a CRM, column a to g, calculations to see who fits criteria are done column h to k.
A pivot table then picks out those who fit the criteria. Vlookup is is then used to populate a seperate sheet with their contact details, last payment, membership duration etc.
The trainer/gym owner then has some manual column to fill out eg. Contacted, date of contact and notes.
So the left part of the new sheet is dynamic (eg, 2, members are on the sheet, member 1 renews and drops of the list, member 2 place in sheet changes OR member 3 enters the criteria and gets placed in between member 1 and 2 --> member 2 position changes in list).
Challenge: My challenge is how to make the notes from the trainer stick to the original record (eg if it moves position --> notes need to move position or dissapears --> notes are erased). Uploading the notes into the CRM is unfortunately not possible.
UPDATE: I found a solution on how to do this in excel (self referencing tables) via Power Query, however have not found an example for how to do it in Sheets.
Last edited by a moderator: