Hi all,
In the below data set, I have four data series (Region 1, Region 2, Sector 1, Sector 2) with varying starting dates. The date range will grow over time as a new column is added each month.
What i'm trying to do is build a function where I can select any of these data series, with say a radio button or similar, and a dynamic chart is populated. using the relevant start date, and end date.
I imagine the first step is creating a dynamic range for each of the 4, which I've struggled with. I've tried using the OFFSET, MATCH then COUNT backwards from the current weight column when creating the width, but couldn't figure it out.
Any help would be greatly appreciated. I've added a picture and also a table below.
In the below data set, I have four data series (Region 1, Region 2, Sector 1, Sector 2) with varying starting dates. The date range will grow over time as a new column is added each month.
What i'm trying to do is build a function where I can select any of these data series, with say a radio button or similar, and a dynamic chart is populated. using the relevant start date, and end date.
I imagine the first step is creating a dynamic range for each of the 4, which I've struggled with. I've tried using the OFFSET, MATCH then COUNT backwards from the current weight column when creating the width, but couldn't figure it out.
Any help would be greatly appreciated. I've added a picture and also a table below.
Asset Allocation | Nov 2020 | Dec 2020 | 2021 | Feb 2021 | Mar 2021 | Apr 2021 | May 2021 | Jun 2021 | Jul 2021 | Current weight | |||
Region 1 | 100.0% | ||||||||||||
North America | 54.7% | 53.4% | 54.3% | 49.9% | 52.9% | 50.8% | 47.2% | 49.1% | 57.2% | 49.9% | |||
Emerging Markets | 16.8% | 18.1% | 20.1% | 18.7% | 15.4% | 15.5% | 16.9% | 15.7% | 11.6% | 18.7% | |||
Europe ex UK | 12.6% | 12.7% | 12.2% | 14.4% | 16.6% | 18.5% | 20.3% | 17.7% | 16.8% | 14.4% | |||
United Kingdom | 4.9% | 5.6% | 5.4% | 5.3% | 5.9% | 5.9% | 6.0% | 6.2% | 5.5% | 5.3% | |||
Japan | 1.1% | 1.1% | 1.1% | 1.0% | 1.3% | 1.3% | 0.0% | 0.2% | -0.5% | 1.0% | |||
Asia Pacific ex Japan | 5.9% | 5.6% | 5.7% | 5.2% | 4.3% | 3.4% | 2.6% | 2.5% | 3.0% | 5.2% | |||
Region 2 | |||||||||||||
North America | 44.4% | 45.4% | 45.7% | 51.7% | 51.8% | 49.7% | 52.8% | 64.2% | 45.7% | ||||
Emerging Markets | 21.0% | 23.8% | 22.2% | 14.8% | 15.1% | 18.9% | 19.1% | 10.9% | 22.2% | ||||
Europe ex UK | 11.4% | 8.4% | 10.6% | 14.1% | 17.1% | 19.9% | 15.2% | 13.8% | 10.6% | ||||
UK | 5.2% | 4.8% | 4.9% | 6.1% | 5.9% | 6.5% | 7.2% | 6.3% | 4.9% | ||||
Japan | 2.7% | 2.6% | 2.4% | 2.9% | 2.7% | -0.5% | 0.1% | -1.6% | 2.4% | ||||
Asia Pacific ex JP | 15.4% | 15.1% | 14.1% | 10.3% | 7.5% | 5.4% | 5.6% | 6.6% | 14.1% | ||||
Sector 1 | |||||||||||||
Consumer Discretionary | 12.2% | 12.9% | 12.5% | 13.5% | 13.4% | 11.9% | 11.9% | 12.9% | |||||
Communication Services | 9.7% | 9.8% | 10.7% | 11.3% | 11.8% | 11.2% | 12.0% | 9.8% | |||||
Consumer Staples | 3.9% | 3.8% | 4.1% | 4.4% | 4.7% | 4.2% | 4.9% | 3.8% | |||||
Energy | 6.0% | 7.2% | 7.0% | 2.5% | 2.6% | 2.3% | 2.1% | 7.2% | |||||
Financials | 17.9% | 19.1% | 14.8% | 14.5% | 15.1% | 21.7% | 14.1% | 19.1% | |||||
Health Care | 5.2% | 4.9% | 6.8% | 6.2% | 5.4% | 5.4% | 7.4% | 4.9% | |||||
Industrials | 3.1% | 3.4% | 4.1% | 4.8% | 4.4% | 4.1% | 4.9% | 3.4% | |||||
Information Technology | 20.2% | 20.6% | 19.5% | 21.4% | 21.5% | 21.1% | 23.6% | 20.6% | |||||
Materials | 2.0% | 2.2% | 2.1% | 2.1% | 1.6% | 1.4% | 1.4% | 2.2% | |||||
Real Estate | 15.3% | 11.8% | 12.8% | 13.8% | 13.4% | 11.6% | 12.2% | 11.8% | |||||
Utilities | 4.4% | 4.5% | 5.5% | 5.5% | 6.0% | 5.2% | 5.3% | 4.5% | |||||
Sector 2 | |||||||||||||
Communication Services | 14.0% | 13.5% | |||||||||||
Consumer Discretionary | 9.0% | 11.3% | |||||||||||
Consumer Staples | 3.5% | 4.4% | |||||||||||
Energy | 12.6% | 2.5% | |||||||||||
Financials | 24.0% | 14.5% | |||||||||||
Health Care | 7.2% | 6.2% | |||||||||||
Industrials | 9.4% | 4.8% | |||||||||||
Information Technology | 2.7% | 21.4% | |||||||||||
Materials | 6.4% | 2.1% | |||||||||||
Real Estate | 6.4% | 13.8% | |||||||||||
Utilities | 4.9% | 5.5% | |||||||||||