Access Beginner
Active Member
- Joined
- Nov 8, 2010
- Messages
- 311
- Office Version
- 2016
- Platform
- Windows
Hi all,
I have below some test data. Which in my version at work is a Pivot Table ( based on over 300,000 rows of data). The data is linked to a query in Access and when new data is imported and the pivot table is refreshed, values of 'Subject may' change. Once the new data has been updated in the Pivot Table, I copy the whole sheet and replace as values, thus removing the Pivot Table and leaving a static table. The raw data is then deleted, as the file is over 250MB with the data included, which is why I cannot use the power of a Pivot Table when sending the document up the chain.
What I do with this is then, create a dynamic table, which is used to create a chart, so a user can select his/her Region and the chart will update.
My issue, as the values of Subject may change, using a vlookup, means I will have to update the column number in each of the formulas. I had used Index and Match, but I cannot get it to work with 3 criteria.
This is a small example of the data, there could be up to 25 subjects and the subject numbers may vary from week to week. My data is run weekly.
I hope I have been clear.
Test data as below
I have below some test data. Which in my version at work is a Pivot Table ( based on over 300,000 rows of data). The data is linked to a query in Access and when new data is imported and the pivot table is refreshed, values of 'Subject may' change. Once the new data has been updated in the Pivot Table, I copy the whole sheet and replace as values, thus removing the Pivot Table and leaving a static table. The raw data is then deleted, as the file is over 250MB with the data included, which is why I cannot use the power of a Pivot Table when sending the document up the chain.
What I do with this is then, create a dynamic table, which is used to create a chart, so a user can select his/her Region and the chart will update.
My issue, as the values of Subject may change, using a vlookup, means I will have to update the column number in each of the formulas. I had used Index and Match, but I cannot get it to work with 3 criteria.
This is a small example of the data, there could be up to 25 subjects and the subject numbers may vary from week to week. My data is run weekly.
I hope I have been clear.
Test data as below
| Excel 2010 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | |||
| 4 | Female | Female | Female | Male | Male | Male | |||
| 5 | Region Name | Subject 1 | Subject 2 | Subject 3 | Subject 1 | Subject 3 | Subject 5 | ||
| 6 | Region 1 | 1202 | 1595 | 1493 | 387 | 564 | 1306 | ||
| 7 | Region 2 | 32 | 853 | 684 | 663 | 374 | 1625 | ||
| 8 | Region 3 | 1669 | 1552 | 1765 | 1351 | 1277 | 1190 | ||
| 9 | Region 4 | 627 | 1512 | 542 | 1143 | 1091 | 749 | ||
| 10 | Region 5 | 1367 | 1510 | 640 | 247 | 492 | 1852 | ||
| 11 | Region 6 | 75 | 1263 | 1945 | 1227 | 1441 | 181 | ||
| 12 | Region 7 | 1474 | 442 | 1219 | 1531 | 1293 | 1808 | ||
| 13 | Region 8 | 1265 | 411 | 1065 | 1032 | 1820 | 409 | ||
| 14 | Region 9 | 651 | 1565 | 1735 | 270 | 0 | 1972 | ||
| 15 | Region 10 | 1481 | 149 | 383 | 1654 | 855 | 1164 | ||
| 16 | Region 11 | 1659 | 1459 | 646 | 1345 | 1049 | 290 | ||
| 17 | Region 12 | 582 | 1494 | 1449 | 1843 | 1085 | 55 | ||
| 18 | Region 13 | 1963 | 1209 | 1698 | 571 | 906 | 974 | ||
| 19 | Region 14 | 1933 | 1013 | 1185 | 1376 | 715 | 1830 | ||
| 20 | Region 15 | 1369 | 1044 | 1747 | 1262 | 10 | 1049 | ||
| 21 | Region 16 | 1760 | 630 | 1679 | 1467 | 285 | 816 | ||
| 22 | All Regions | 19109 | 17701 | 19875 | 17369 | 13257 | 17270 | ||
| 23 | |||||||||
| 24 | Expected results when All Regions is selected ( data validation) | ||||||||
| 25 | Female | Female | Female | Male | Male | Male | |||
| 26 | Subject 1 | Subject 2 | Subject 3 | Subject 1 | Subject 3 | Subject 5 | |||
| 27 | All Regions | 19109 | 17701 | 19875 | 17369 | 13257 | 17270 | ||
Sheet1 | |||||||||
