CODEWITHSUNDEEP
excelexcel-formulaformula
kenkenhimself
kenkenhimself

Reputation: 71

Dynamic Excel formula

I have a master table that feeds 4 types of codes and sub-tables for each code. I would want a dynamic formula for each sub-table in such a way that whatever new code is entered in the master table, the formula will automatically add the new entry to the corresponding sub-table.

Example: If I enter code A next to code B in the master table, the new code will display under sub-table A along with its info1 and info2 entries.

enter image description here

I am not a native English speaker so I hope my explanation didn't complicate things. :)

Note: The tables aren't limited to three columns.

Upvotes: 0

Views: 95

Answers (2)

kenkenhimself
kenkenhimself

Reputation: 71

I come up with a formula that does not include Index, but will need another column as a sort of 'index'.

{=IFERROR(SMALL(IF($AB$11:$AB$100=$BG$9,$AJ$11:$AJ$100),$BB11),"")}

Where $BB11 is the number 1 for the first list. Works like a charm!

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Turn the data entry table into an Excel Table object with Insert > Table.

Now you can build three pivot tables that show Info1 and Info2. Filter each pivot table to show the code A, or B, or C respectively.

When you enter new data into the data entry table, use Data > Refresh All and the pivot tables will update. You can create a worksheet change event that will automate updating the pivot tables when a cell in the data entry table is changed.

enter image description here

A formulaic approach is only possible by pre-filling a fixed range with an array formula. In the following screenshot, the yellow cells in column E were selected, the following formula was entered and confirmed with Ctrl-Shift-Enter:

=IFERROR(INDEX(B$1:B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)),ROW()-2)),"")

Copy to column F.

enter image description here

In column H the formula is

=IFERROR(INDEX(B$1:B$20,SMALL(IF($A$1:$A$20=$H$1,ROW($A$1:$A$20)),ROW()-2)),"")

Copy to column I.

In column K the formula is 

=IFERROR(INDEX(B$1:B$20,SMALL(IF($A$1:$A$20=$K$1,ROW($A$1:$A$20)),ROW()-2)),"")

copy to column L.

Note that these array formulas will not allow editing a single cell. You will always have to select all the cells with the same array formula, edit and then confirm again with Ctrl-Shift-Enter.

Once your data in columns A to C goes beyond row 20, you will need to adjust the array formulas accordingly.

This approach is not very efficient, since the pre-filled formulas will always calculate, regardless of the number of rows present in the data entry columns.

Note that array formulas should not use whole column references, since that will slow down the workbook considerably.

The Pivot tables approach will be way more efficient, requires less setup work and no maintenance if set up correctly.

Upvotes: 2

Related Questions