Forum Discussion
Solved
How to create a line chart from multiple Excel sheets
Hi, For student rewards (Bronze, Silver, Gold). I need to create a line chart which tracks progress over several weeks. Each week data is on a separate Excel sheet. I want the line chart to ...
- The y-values of a chart must be numeric. So change Bronze to 1, Silver to 2 and Gold to 3.
- However, you can use the custom number format [=1]"Bronze";[=2]"Silver";"Gold" to display them as Bronze, Silver and Gold.
- You'll have to create a new range to act as data source for the chart. In the attached example, I used VLOOKUP combined with INDIRECT to do this.
- The y-values of a chart must be numeric. So change Bronze to 1, Silver to 2 and Gold to 3.
- However, you can use the custom number format [=1]"Bronze";[=2]"Silver";"Gold" to display them as Bronze, Silver and Gold.
- You'll have to create a new range to act as data source for the chart. In the attached example, I used VLOOKUP combined with INDIRECT to do this.
HansVogelaar thank you 
I really appreciate your help. This works for me!
However how do I change the custom number format? I get a "cannot use the number format you typed" error each time I try to change it?
I need [=0]"4th Place";[=1]"Bronze";[=2]"Silver";[=3]"Gold";
It will let me do [=0]"4th Place";[=1]"Bronze"; but nothing else?
Thank you