Creating a chart referencing data in multiple sheets using a drop-down list of sheet names
I need help in creating a date vs score chart in excel. The issue is that the chart should have a drop-down list containing the name of the sheets which have the data in it in such a way that when a sheet is selected from the list the chart displays the data regarding that particular sheet. I don't mind using macros to accomplish the task, so please guide me on how I can do it. I have attached the Sample data and Desired Result.
Last edited by dops; 07-23-2017 at 06:32 PM.
Create a cut-down version of the file with two or three sheets and a mock-up of what you require -attach that here.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I want to create a dynamic line chart with a drop down for student name(name of the worksheets), to display one student at a time. The chart will plot total score over time by booklet number (1 – 18, 19 – 30). The chart range is A7 to Z column. The dates(x-axis) are in A column, booklet numbers(series) in C column and total score(y-axis) in Z column. Since the dates can be added there is no limit to the number of rows. There are about 50 students so 50 worksheets worth of data that I want to compile in the chart. There should also be the ability to add new students. Please any help will be appreciated.
Will you please attach a sample Excel workbook? A smaller version of it will be fine.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution is also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Hi here are the attachments as requested. The desired result shows what do I expect the chart to be like after I select a student from the drop-down list. What I also want is to someway create a Add Student button which when pressed will automatically create a new sheet copying the sheet format, insert the student's name in the drop-down list of the chart. In the original document, there are about 50 sheets like this.
Last edited by dops; 07-23-2017 at 12:58 AM.
Could you attach file with example chart (with all option you described above) for eg. student1 ? (with dummy data)
Hi I have attached the sample data and desired result in the reply post above.Originally Posted by sandy666
Why not add all data in 1 sheet. It makes it a lot easier to analyse the data.
You can add 1 column in which you store the name of the student.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I'd like to see it in Excel (working)
btw, you set the same dates for Booklet 1-18 and Booklet 19-30. It should be like this?
or your example is not correct
that is why I'd like to see real example with correct data and working chart with data in A and C column. For Z I'll generate data by myself.
Last edited by sandy666; 07-23-2017 at 11:14 AM.
No the example is correct. The subject content of the booklet ranges are different. The normal practice is for student to do 1 booklet each from the ranges, it though sometimes varies depending on the progression of the student.
so 1st booklet is from row 6 to 23 and 2nd from 24 to 35 and dates are the same for both?
I think example with data is required
Last edited by sandy666; 07-23-2017 at 11:39 AM.
I have attached the dummy data you requested and an image of how the chart of that dummy data would look.so 1st booklet is from row 6 to 23 and 2nd from 24 to 35 and dates are the same for both?
I think example with data is required
okfor one student
okdummy data
not ok.working chart for both booklets
sorry, my patience is off.
good luck
ok what if we create 2 different working charts for both booklets so one for each booklet type.
Thanks to everyone
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks