0

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)?

  • Look Into formulas, something like: =B$1 * B5, populated down in table 2 should work (assuming the calculation. – cybernetic.nomad Oct 18 '18 at 17:32
  • @cybernetic.nomad I know about multiplying cells, but that doesn't take into account conditionally generating the table values with labels. Unless some automatic filter can be created to only include values >0 and their labels? – Winterflags Oct 18 '18 at 17:34
  • To only have values above Zero: =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. – cybernetic.nomad Oct 18 '18 at 17:41
  • @cybernetic.nomad I edited my example for clarity, by adding more rows. – Winterflags Oct 18 '18 at 17:48
  • This could be done pretty easily with VBA, if that's something you're willing to try. – Zack Oct 18 '18 at 18:37
1

OP here — I was able to make this work pretty OK now, so I'm including my "solution" for reference. However, i'm not sure if it's the best approach.

I created two tables, with Table 2 calculations based on the relevant formulas from Table 1.

Then I added a Filter to Table 2, displaying only rows with values Greater Than 0 based on cells in the appropriate column. To add a filter, press the down arrow of the table column you wish to filter by.

This filters out rows that have empty values; however it does not dynamically update the filtering when Table 1 data changes.

To make the Table 2 dynamically update based on inputs in Table 1, I saved the file as an Macro-enabled Excel file (Save As > Excel Macro-Enabled Workbook, .xlsm), and added the VBA code from this answer: https://superuser.com/a/501736/455679

To add VBA code, right-click on the Sheet on the bottom bar of Excel (typically Sheet1), and click View Code from the context menu. Then paste and save the following code, where Table2 is supposed to be the name of the actually filtered table:

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table2")
         .AutoFilter.ApplyFilter
    End With
End Sub

Unfiltered values:

enter image description here

Dynamically filtered values:

enter image description here

Note that because of the row filtering, Table 1 and Table 2 should not share the same rows – as that could filter out Table 1 based on its own values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.