In Excel, I would like to generate a dynamic table, based on the values in some other cell.
I have never done this before, so I don't know the terminology or the techniques involved. I'm also not sure if Excel is able to accomplish what I want it to.
Here's what I'd like to do:
1 - I enter a value into the cell next to Sum
(here it's $100
).
2 - I then distribute a percentage of 100% to A
, B
, C
, D
:
[Table 1]
|------|--------------|
| Sum | $100 |
|------|--------------|
|------|--------------|
| Unit | Distribution |
|------|--------------|
| A | 0.5 |
|------|--------------|
| B | |
|------|--------------|
| C | |
|------|--------------|
| D | 0.5 |
|------|--------------|
A
and D
has 50 % each, and B
and C
have 0%. The above table structure should always be the same.
Now, I'd like to automatically generate a second table dynamically, based on the inputted cell values in Table 1, which gives the output:
[Table 2]
|------|--------------|
| Unit | Value |
|------|--------------|
| A | $50 |
|------|--------------|
| D | $50 |
|------|--------------|
By dynamically generating the table, I mean that only the values of A
and B
are generated, while C
and D
are not generated because their distribution values were 0%.
On the other hand, a different distribution would generate another table:
[Table 1]
|------|--------------|
| Sum | $100 |
|------|--------------|
|------|--------------|
| Unit | Distribution |
|------|--------------|
| A | 0.75 |
|------|--------------|
| B | |
|------|--------------|
| C | 0.125 |
|------|--------------|
| D | 0.125 |
|------|--------------|
[Table 2]
|------|--------------|
| Unit | Value |
|------|--------------|
| A | $75 |
|------|--------------|
| C | $12.50 |
|------|--------------|
| D | $12.50 |
|------|--------------|
Why I want to generate Table 2 dynamically: Because in reality I have a large number of potential "Units" to distribute to, with some different arithmetics, and I only wish to get a coherently tabled output which has values > $0. I will copy/paste these figures into my bookkeeping application.
Questions:
1) What is the correct "terminology" and "techniques" for what I'm trying to accomplish? I'm asking this, to be able to find resources online, in case no one can help me with my specific use case. Do I use something like Pivot Tables, Dynamic Ranges, etc? (I have no experience with these). I would greatly appreciate as much specificity as possible.
2) Best case, can you even help me with an example solution for the above needs? If not fully, are there some certain Excel tools and/or formulas I should know about (something like dynamic filtering tables for values >0)?
=B$1 * B5
, populated down in table 2 should work (assuming the calculation.=IF(B$1&B5>0, B$1*B5,"")
, though at this point I am less and less sure of what you want. You may want to check this out so we can all be sure we are talking about the same thing.