Debtors follow up spreadsheet

[jayjay123]

Hi there,

I am trying to create a debtors follow up spreadsheet. It will need to be updated weekly, and allocated across the team.
The spreadsheet will have reoccurring customers which have not paid bills to the company. My team will need to follow up and write commentary/notes for the customer.

Whats the best way to design an interactive spreadsheet for this?
Ideally, if I have one outstanding customer, I'd like the commentary to update on all future outstanding items.

Thanks

 

[Devo182]

Vague question - so hard to give any specific advice.

Jmo I would build a form with the data hidden. With the form a team member could add a debtor or pull up a debtor to either make/add to a current note or remove from the list as paid. Would only need simple lookup formulas for the most part.

 

[jayjay123]

Hi Devo182!

Thanks very much for your reply.
Your perspective sounds very interesting. Is there somewhere I can learn this?

Hoping to create something like this or yours if it is better ....

Outstanding (tab)
- List of clients that need to be contacted to recover funds
- Can I use a power query to update part of the data (from columns A - E) then update columns F-G manually/using a vlookup? Sorry never used power query before, but wondering if this could be an option.
- If I update a comment for one particular ID (e.g. ID 123 & comment in H4), can this comment H4 be automatically updated to all IDs?

Recovered List (tab)
- Once the funds have been recovered from the Outstanding (tab), manually transfer the item to the Recovered (tab)

Summary (tab)
- Can I add a filter formula to retrieve all the data from Outstanding (tab) + Recovered (tab)?
- Is there a formula I can add to retrieve column J, to show which tab it is coming from?
- Thinking of having a search option in cell B2 & B3, is this possible?

Thanks so much!

 

[jayjay123]

data in column A-E is from a bank statement