I'm simply requesting ideas on how to structure or setup a scheme for what I'm describing below; not requesting anyone to build anything for me at this point.

We serve as case managers for several hundred lawsuits at any given time and need a more convenient means of tracking negotiations and final resolutions. Up to this point, I have been tracking my appeals essentially on individual paper sheets, per case; although, I use Excel for preparing the evidence that I provide and several other tasks related to our work.

I have created a VBA-based tracking/log system for moving from paper to an Excel-based workbook and most of the data points are being input into a worksheet by means of a Userform and related code. Each row of the Log worksheet represents a single account in a lawsuit. Each account has a unique account number and has a Cause Number (case number) associated with a Year (2014, 2015, 2016) that could be used as a Key for connecting the cases with their accounts. (CauseNo + Account + Year)

One of the remaining tasks which I cannot figure out a good way of managing with my new Log system are the settlement offers.

I need a way to log the offers (To the Defense / From the Defense) for each record (account) on the worksheet. The sequence of offers can go like:

To - $3,425,000
From - $6,875,000
To - $4,000,000
From - $6,500,000
Final settlement is $5,500,000 (or such)
And can also start with From, if it comes from the Defense first, then To . . . .

A second XLSX could exist in the workbook to hold the offers. One column for the first To-offer, the next column would be for the From-offer, and so on across the sheet. Each row would tie to its account on the Log sheet. I would need to create the initial entry on the Offers sheet once negotiations start and bring up a Userform to input the next offer Sent/Received. This is just impromptu planning here.

Any suggestions on this?

Thank you,
Delain