I am trying to build a Dynamic Chart based on data in Sheet2. I have tried to research this myself but all the tutorials show data that is displayed and grows in Columns and my data is contained in Rows. I need to show a comparison between the class average and a given student based on dropdown list. Your help is appreciated.
Put this in B4 of Sheet2:
=VLOOKUP($A4,Sheet1!$A$8:$Z$47,COLUMNS($A:B),FALSE)
then copy across.
Hope this helps.
Pete
See if this works for you...
HTH
Regards, Jeff
Hey Jeff. Thank you for the submission. This is moving in the right direction of where I want to go. Do you have a link to a tutorial I can use to better educate myself on how this works?
Ken
Hi Ken,
Well I can't point exactly to a site to do what I did, but this is a good place to start...
https://peltiertech.com/Excel/Charts...umnChart1.html
https://peltiertech.com/Excel/Charts/index.html
https://peltiertech.com/dynamic-charts/
The method I used is just something I've picked up over time...
Last edited by jeffreybrown; 10-04-2018 at 08:14 PM.
Note that you still need to make the change to your formula in B4 that I suggested in Post #2 - as it stands, you are picking up Susie's marks, not Sally's.
Pete
Good catch, thank you Pete.
Jeff,
So I tried to figure this out using very basic data. I created two formulas but cannot get a chart to accept them. Can you take a look and provide some guidance please.
Thank you,
Ken
Hi Ken,
What you have will work, you just need to replace the quotes from smart quotes to straight quotes.
From this...
=OFFSET(Charts!$B$2,,,COUNTIF(Charts!$B$2:$B$100,”<>”))
To this...
=OFFSET(Charts!$B$2,,,COUNTIF(Charts!$B$2:$B$100,"<>"))
But what you are using is limiting the data to row 100. Why not make it more dynamic?
=OFFSET(Charts!$B$2,,,COUNTA(Charts!$B:$B)-1,1)
Better yet though, so you don't even have to mess with named ranges, convert your data into a Table and then create the chart from the Table.
https://www.techrepublic.com/blog/mi...arts-in-excel/
Last edited by jeffreybrown; 10-05-2018 at 10:06 AM.
Hey Jeff,
Holy Cow! Smart quotes vs straight quotes. That would have taken me a month of Mondays to figure out. Thank you for your guidance and lessons. I will run with what you have shown me and see how far I can go.
I appreciate your patience and inputs.
Ken
You are most welcome Ken. Happy to help. Come on back if you get stuck or need a nudge.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks