Forum Discussion
Multiple tables automatically updated
Hi Expertise,
May I ask your help in this case?
I have 3 similar tables under Section 1, Section 2 & Section 3 as below. When i select desire value from a cell with list, select service either A or B, all tables can be automatically updated.
Could you please advise how to achieve
| Section 1 | |
| Department | Expenses |
| Q | 10 |
| R | 11 |
| Y | 18 |
Department | Section | Service | Expenses |
| Q | 1 | A | 10 |
| R | 1 | A | 11 |
| S | 2 | A | 12 |
| T | 2 | B | 13 |
| U | 3 | B | 14 |
| V | 1 | B | 15 |
| W | 2 | A | 16 |
| X | 3 | A | 17 |
| Y | 1 | A | 18 |
Lawrence
1 Reply
Take this:
- Create a Dropdown for Service Selection
- Go to a cell (e.g., D1) and use Data Validation:
- Data → Data Validation → Allow: List → Source: A,B
- Use FILTER or INDEX/MATCH to Populate Tables
Assuming your full data is in a table named DataTable with columns: Department, Section, Service, Expenses.
You can use the FILTER function (Excel 365 or Excel 2021):
=FILTER(DataTable, DataTable[Service]=D1)This will return all rows where Service matches the selected value.
To break it down by Section (1, 2, 3), use:
=FILTER(DataTable, (DataTable[Service]=D1)*(DataTable[Section]=1))Repeat for Section 2 and Section 3 by changing the Section value.
- Display in Separate Tables
Place each formula in the starting cell of your Section 1, 2, and 3 tables. Excel will spill the results automatically.
