A "Bouncing-Ball" Headache

[Skiier89]

Hello,

I am attempting to resolve an issue with tracking (and thus recalling) certain attributes that are transferred between records in a workbook. Here are the details -

Sheet 1 contains data for owners in a business. Names are vertically listed in column A, and each name (row) has corresponding values in columns B, C, D and so on. The values represent each person's ownership in a business over the course of time. Essentially each name is a record.

Subsequent worksheets in the workbook isolate year-by-year segments from the data on Sheet 1, and display the relevant owners (those with a > 0% interest) for that year. A new worksheet for the current year is created by copying the prior year worksheet, and a filter adjusts to display relevant owners. Calculations for the current year are preformed on the worksheet. As time goes on and ownership is transferred from old owners (transferors) to new owners (transferees), the old owners fall off the current year worksheet.

The issue is that their are certain attributes that must follow the transfer. For example, if owner 1 transfers to owner 2 in year 5, the prior 4 years of i.e. profit share must be associated and reported under the new owner (owner 2), even though the old owner (owner 1) is no longer an owner displayed in the current year. It's a "bouncing-ball" problem, almost like tracking a serial number of a dollar bill over time as it transfers ownership from buyer to seller and so on.

I've tried working out numerous ideas on how to accomplish this in a relative "automatic" fashion - that is without a user having to manually go back to prior years and link the attributes that transferred. The transferors (old owners) must be associated with the transferees (new owners) in a way to easily recall the (old) transferor's transferred attributes under the (new) transferee's name, even when the transferor is no longer relevant in the current year (or only to the extent there were attributes that transferred.)

Perhaps this is something best done in another program, but as of now I am limited to Excel. Macros are not an availably option either.

Greatly appreciate any and all ideas or suggestions. Thank you in advance.

 

[Gerald Higgins]

Hi, I don't think this is really an Excel problem as such, it's more of a maths or accounting problem, and you need to work out for yourself how you want to do it in principle.
Once you're clear about that, then maybe Excel can do it for you, or maybe, as you say, some other tool might be better.

Anyway, it sounds like what your are describing is something like company shares, so how about setting up records that represent each individual share ?
If each owner's share is equal in size to every other owner's share, then you can set the shares at that level.
If it's more complicated than that, then you might want to deal with much smaller individual shares.
For example, if one owner can transfer say 25% of their ownership to one other owner, and also 33% to a second owner, then you might want to have lots of very small value shares.

Then I think you need to decide a start point - either today, or some date in the past - and allocate these individually numbered shares to each owner in proportion to their ownership on that date.

Make sense ?