dynamic chart

[billhealth]

I have an excel spreadsheet listing the values of crypto coins by date.
1/25 1/26 1/28 2/1
BTC 1600 1726 1659 1640
ETH 804 809 1240 936
CRO .21 .24 .22 .24

(NOT REAL DATA)

There are 65 coins i have been tracking for over a year. I have inserted charts to show the ranges.
However, 65 charts are quite messy! I would like to select the cell marked BTC or other coin and have one chart which will change based on the label in the cell.

I tried to use VBA but have been completely unsuccessful. If you have any suggestions, it would be very helpful. Thanks, BH

 

[RasGhul]

Hi Bill,

Maybe you could use this, I recommend converting your data to a table so we can make the index match formulas expand with your data dynamically.

When inserting your chart make sure to highlight the chart output range more columns than needed, and drag out the index match formula the same.

The dates will automatically spill as you add more dates to the data table.

Book2
	A	B	C	D	E
1		25/01/2021	26/01/2021	28/01/2021	1/02/2021
2	BTC	1,600	1,726	1,659	1,640
3	ETH	804	809	1,240	936
4	CRO	0.21	0.24	0.22	0.24
5
6
7		25/01/2021	26/01/2021	28/01/2021	1/02/2021
8	BTC	1,600	1,726	1,659	1,640
9
Sheet1

Cell Formulas
Range	Formula
B7:E7	B7	=OFFSET($B$1,,,1,COUNT(1:1))
B8:E8	B8	=IFNA(INDEX($B$2:$E$4,MATCH($A8,$A$2:$A$4,0),MATCH(B$7,$B$1:$E$1,0)),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A8	List	=$A$2:$A$4