OK, I hope I can make this clear - I think what I have here is a "data
analysis vs. data presentation" issue. I've got an Excel table that
matches people (column A) against how much they owe in any of, say 10
different yearly periods (row 1). The interior cells show, for
example, Joe (A10) owes $100 from 1980 (E10). So far, so good.

There are three complications.

First, the money that is owed by Joe, and everyone else, is owed to a
completely separate group of people, each of whom is going to get a
share of a yearly total, according to a fixed percentage. For
example, Bob is going to get 5.5% of what Joe, and all the other
debtors, owe in 1980, but he might only get 4.1% of what Joe, and
everyone else, owes in 1981. At this point, we're using the lower
section of the very same worksheet to perform and display these
"breakout" calculations. The creditors are lined up vertically in
Column A, just like the debtors, but below them. And we're printing
the whole thing out as a gigantic blob to show to the creditors so
they know how much they've got coming.

Second, I now find out that I need to subject the amounts owed by Joe
and the debtors to some hypothetical discounts. That is, I've been
asked to add extra columns alongside each yearly column showing what
Joe would owe if he got a 10%, or 20% discount on his debt. So now my
spreadsheet is 3 times wider than it used to be.

Third, I now find out that there are two new groups of creditors, such
that for a given year there may be two or three completely independent
groups of people to whom portions of that year's debt are owed. So
now my spreadsheet is twice as tall as it used to be.

So my question is, is it possible to somehow automatically, or
programatically, show just one "set" of the yearly columns at a
time? Given my description, does it sound like I'm going to be able
to make this all work together, or am I at a point where I will need
to put all of the data in one worksheet, and split the different
yearly projections out into separate presentation spreadsheets? Most
basically, how do people cope with situations where two completely
separate sets of calculations (debts by debtor by year vs. shares by
creditor by year) have to coexist on the same worksheet? It seems
messed up that changing the formatting or whatnot in the top portion
of a column might accidentally change something in the completely
unrelated lower portion of that same column - I have a feeling there's
something quite fundamental that I am missing in using Excel -

Thanks much -

Guest3731 wrote:
> OK, I hope I can make this clear - I think what I have here is a "data
> analysis vs. data presentation" issue. I've got an Excel table that
> matches people (column A) against how much they owe in any of, say 10
> different yearly periods (row 1). The interior cells show, for
> example, Joe (A10) owes $100 from 1980 (E10). So far, so good.
>
> There are three complications.
>
> First, the money that is owed by Joe, and everyone else, is owed to a
> completely separate group of people, each of whom is going to get a
> share of a yearly total, according to a fixed percentage. For
> example, Bob is going to get 5.5% of what Joe, and all the other
> debtors, owe in 1980, but he might only get 4.1% of what Joe, and
> everyone else, owes in 1981. At this point, we're using the lower
> section of the very same worksheet to perform and display these
> "breakout" calculations. The creditors are lined up vertically in
> Column A, just like the debtors, but below them. And we're printing
> the whole thing out as a gigantic blob to show to the creditors so
> they know how much they've got coming.
>
> Second, I now find out that I need to subject the amounts owed by Joe
> and the debtors to some hypothetical discounts. That is, I've been
> asked to add extra columns alongside each yearly column showing what
> Joe would owe if he got a 10%, or 20% discount on his debt. So now my
> spreadsheet is 3 times wider than it used to be.
>
> Third, I now find out that there are two new groups of creditors, such
> that for a given year there may be two or three completely independent
> groups of people to whom portions of that year's debt are owed. So
> now my spreadsheet is twice as tall as it used to be.
>
> So my question is, is it possible to somehow automatically, or
> programatically, show just one "set" of the yearly columns at a
> time? Given my description, does it sound like I'm going to be able
> to make this all work together, or am I at a point where I will need
> to put all of the data in one worksheet, and split the different
> yearly projections out into separate presentation spreadsheets? Most
> basically, how do people cope with situations where two completely
> separate sets of calculations (debts by debtor by year vs. shares by
> creditor by year) have to coexist on the same worksheet? It seems
> messed up that changing the formatting or whatnot in the top portion
> of a column might accidentally change something in the completely
> unrelated lower portion of that same column - I have a feeling there's
> something quite fundamental that I am missing in using Excel -
>
> Thanks much -

This is a great example of multidimensional analysis. You have debtors,
creditors, years, discounts by all the aforementioned, and a value =
amount owed.

On the face of it, a pivot table might work well here. Pivot tables are
all about summarizing values within multiple breakouts, as long as you
have all the detail to support it (and it sounds like you do).

I don't quite know where to tell you to start though. Do you have,
somewhere,

Debtor, Creditor, Year, Amount Owed
Creditor, Year, Discount

?

On Aug 28, 7:37 pm, smartin <smartin...@gmail.com> wrote:
[snip]
> I don't quite know where to tell you to start though. Do you have,
> somewhere,
>
> Debtor, Creditor, Year, Amount Owed
> Creditor, Year, Discount
>
> ?

Sure do. I've got data coming out of my ears - I just need to get my
presentation in order. Sounds like I need to look into pivot tables a
bit!

Thanks very much for the reply -

Guest3731 wrote:
> On Aug 28, 7:37 pm, smartin <smartin...@gmail.com> wrote:
> [snip]
>> I don't quite know where to tell you to start though. Do you have,
>> somewhere,
>>
>> Debtor, Creditor, Year, Amount Owed
>> Creditor, Year, Discount
>>
>> ?
>
> Sure do. I've got data coming out of my ears - I just need to get my
> presentation in order. Sounds like I need to look into pivot tables a
> bit!
>
> Thanks very much for the reply -

Good deal. Fire back if you have questions.

Aside, if you are not already acquainted with pivots, start with some
simple data to understand the territory, then attack your beast (^: