vampsthevampyre
New Member
- Joined
- Apr 29, 2016
- Messages
- 20
- Office Version
- 365
- Platform
- Windows
Afternoon All,
Can anybody help I'm looking to produce a table that has the members ranked by score for each year.
The year is dynamically created as is the main array data, and I have manually created the first, second and last year in the lower table but I'm looking to automate the production of the final table.
Any help would be appreciated
Regards
Ian
Can anybody help I'm looking to produce a table that has the members ranked by score for each year.
The year is dynamically created as is the main array data, and I have manually created the first, second and last year in the lower table but I'm looking to automate the production of the final table.
Any help would be appreciated
Regards
Ian
| Premium bonds.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| BF | BG | BH | BI | BJ | BK | BL | |||
| 9 | |||||||||
| 10 | 2021 | 2022 | 2023 | 2024 | |||||
| 11 | Ian | 25 | 450 | 1300 | 800 | ||||
| 12 | Jennie | 0 | 675 | 1650 | 825 | ||||
| 13 | Daniel | 0 | 875 | 2500 | 775 | ||||
| 14 | David | 0 | 500 | 1525 | 575 | ||||
| 15 | |||||||||
| 16 | |||||||||
| 17 | 2024 | 2023 | 2022 | 2021 | |||||
| 18 | Ian | 800 | 1300 | 450 | 25 | ||||
| 19 | Jennie | 825 | 1650 | 675 | 0 | ||||
| 20 | Daniel | 775 | 2500 | 875 | 0 | ||||
| 21 | David | 575 | 1525 | 500 | 0 | ||||
| 22 | |||||||||
| 23 | 2024 | 2023 | 2022 | 2021 | |||||
| 24 | Jennie | Daniel | Ian | ||||||
| 25 | Ian | Jennie | Jennie | ||||||
| 26 | Daniel | David | Daniel | ||||||
| 27 | David | Ian | David | ||||||
| 28 | |||||||||
Setup | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| BH10:BK10 | BH10 | =BH2# |
| BG11:BG14 | BG11 | =Table2[Column headers] |
| BH11:BK14 | BH11 | =OFFSET(Results!BL9,,,4,BF4) |
| BH17:BK17 | BH17 | =BH1# |
| BG18:BG21 | BG18 | =BG11# |
| BH18:BK21 | BH18 | =INDEX(BH11#,MATCH(BG18#,BG11#,0),MATCH(BH17#,BH10#,0)) |
| BH23:BK23 | BH23 | =BH1# |
| Dynamic array formulas. | ||
