How-To Geek

Hello to all,
Here is a problem relating charts in Excel.

In above figure, I would like to solve two problems.
1. Firstly, I want to quickly make the graph of all roll nos. separately(like the one i have shown for roll no.101).
Some kind of formula or programming etc. so that automatically separate charts for each roll no. containing same variables (like Test 1 marks %age, Test 2marks %age, etc.) are drawn quickly.

2. Secondly, when charts are being made in this way automatically (which of course i know the geeks here are capable here to do it), then i want that only that chart become visible to me for which i clicked on the cell of that specific Roll No..
Example when i click on the cell of roll no. 105 ( i.e.. A6) the the chart corresponding to Roll No.105 pops out or is displayed next on right side (as shown for roll no. 101 in grey colour) .
When i click on Roll No.106 (i.e.. A7) then only chart of 106 is displayed, the previous one of roll no. 105 should someway hide or be invisible.
Though i know hyperlink is one of the option for second problem, but it would be very comfortable if the chart is someway not displayed in some another window (like in another excel sheet or picture manager etc.) but it should pop out right next to the roll no. clicked in same excel sheet.

Do Note one thing that i have hide some column cells which actually contained actual marks of Tests Or Assignments. The column cells that are visible to you have percentages formulas applied to them.

Thanx for reading patiently, I hope I have made myself clear of the problem i m facing.
Waiting eagerly for the solution from the geniuses........

Hi Amrin

I'm not sure why you want to generate all charts but there is a way to dynamically change the chart depending on the Roll No cell selected.

Here's how I did it

First I set up a series of vlookups to get the data from a single row for the chart. I've set up J1 as the Roll number reference and then in j2 to q4
I set up my vlookup references as in the following picture (when your done setting up the vlookups you can cover it up with the chart

The vlookup in J4 is

=VLOOKUP($J$1,$A$2:$H$28,J2,FALSE)

And copy that across to Q4

Then create your chart using J1 as your title reference and K3 to Q4 as your chart data

Finally create this macro in Sheet1 (or whatever sheet that contains the data)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

LastRow = Range("A1").End(xlDown).Row

If Target.Column = 1 And Target.Row > 1 And Target.Row <= LastRow Then
Range("J1") = Target
End If

End Sub

Now whenever you click on a Roll No the chart will change to show that row of data. Here are a couple of examples

@ moreeg - I tend to use VBA rather than formulas but what you have here looks simpler (if only I would take the time to learn!). Just wonder how you adjust your formulas to take into account the hidden columns that the OP has.

Thanks moreeg, appreciate the explanation. That is slick well done!

moreeg,

Very elegant solution. I have made minor modifications that should make the chart flow correctly based on the presentation of the data. It took me a bit to understand the logic, but after working it out I don't think a purely macro/VBA based solution would work better. I stand by your solution. You have done very good work.

amrinderminhas,

It may be hard to follow moreeg's setup. Let me see if I can make it clearer what is being done.

Your data is in cells B1:N(n), where (n) is the last row of your data with some columns hidden because you don't want them on your chart. Your headings are in cells A1:N1. The first thing you should do is name your data range, B1:N(n). I named the range RollData, you will see why in the VLOOKUP formulas.

In the area P1:W4 moreeg set up formulas to copy the data relevant to the row you click for the Roll No. that you want to chart. This way he can base the chart on the right information.

In P1 we place the Roll No. so that we have it to look up in the data to extract the other information for the chart. This is the first thing the macro does, copy the Roll No. from the cell we click on to P1.

P2:W2 contain the column offsets that we need for VLOOKUP to find the data for the chart. I put them in backwards so that the chart data is in the same form as the table data, so:

P2=1, Q2=14, R2=13, S2=11, T2=9, U2=7, V2=5, W2=3

Below that information I copied the heading information, keeping the same backwards relation to the table, so:

P3: =A1, Q3: =A14, R3: =A13, S3: =A11, T3: =A9, U3: =A7, V3: =A5, W3: =A3

Then, finally, the VLOOKUPs that populate the series data for the chart:

P4: ="Roll No. "&VLOOKUP($P$1,RollData,P2,FALSE)
Q4: =VLOOKUP($P$1,RollData,Q2,FALSE)
R4: =VLOOKUP($P$1,RollData,R2,FALSE)
S4: =VLOOKUP($P$1,RollData,S2,FALSE)
T4: =VLOOKUP($P$1,RollData,T2,FALSE)
U4: =VLOOKUP($P$1,RollData,U2,FALSE)
V4: =VLOOKUP($P$1,RollData,V2,FALSE)
W4: =VLOOKUP($P$1,RollData,W2,FALSE)

Naming the data range makes the VLOOKUP easier to read.

Now, for the macro, I did not change moreeg's code except to make the highlight extend all the way to column N, the last column of the data table:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
LastRow = Range("A1").End(xlDown).Row
If Target.Column = 1 And Target.Row > 1 And Target.Row <= LastRow And Target.Cells.Count = 1 Then
    Range("P1") = Target
    With Range("A2", Range("A1").Offset(LastRow, 13)).Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Range(Target, Target.Offset(0, 13)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.149998474074526
        .PatternTintAndShade = 0
    End With
    Cells(Target.Row, Target.Column).Select
End If
End Sub

Finally, once this has been done, you can create a chart based on the data in cells Q3:W4 with the heading coming from P4. You will notice that I prepended the "Roll No. " text to the numeric, making the chart easier to understand. Format the chart however you want. If you still have questions about this, feel free to post.

amrinderminhas,

I don't remember what version of Excel you are using, but try this:

Down at the bottom of the screen, right-click the tab name ("Sheet1" or whatever you have named it). On the submenu there should be a selection "View Code". You should click on this to bring up the code page for the sheet you are on. This is the place to Paste the macro code that moreeg presented. After pasting the code, click the "X" in the upper right to save and exit the code page.

Try that and get back to us.

Hi Amrinder, Enthusiast and nosparks

You can download my Excel file from here ....

Download

This will certainly be better than my explanations.

Excellent!! thanks for testing again, warlock. I'm really hoping this will help provide better assistance for Excel questions and maybe even draw more users to the site.

Thanks Warlock, I think so too. And it only took me 2.5 years to figure this out. I'm sure someone here has already posted this capability somewhere on the site and will laugh at my ingenuousness.

@moreeg, thanks for serving that up. It's obvious you really enjoy this stuff.

I've never had anything to do with charts before, didn't even know there was a ribbon of chart tools. Learn something new every day.

Thanks again
NoSparks