SEARCH

The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

(Solved) - Automatically making Bulk of Charts for the large excel sheet &then Customizing

(27 posts)
  • Started 2 years ago by amrinderminhas
  • Latest reply from amrinderminhas
  • Topic Viewed 2797 times

amrinderminhas
Posts: 59

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........

Posted 2 years ago
Top
 
Enthusiast
Enthusiast
Posts: 566

Posting to let you know that you aren't being ignored. No solution as yet, but putting some thought into it.

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

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

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

Some improvements to the macro - will shade in the selected row and disregard multiple cell picks

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("J1") = Target

With Range("A2", Range("A1").Offset(LastRow, 7)).Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With

' Range(Target, Target.Offset(0, 7)).Select
With Range(Target, Target.Offset(0, 7)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With

Cells(Target.Row, Target.Column).Select

End If
End Sub

Posted 2 years ago
Top
 
nosparks
Posts: 148

@ 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.

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

Hey nosparks

That made me smile; "I tend to use VBA rather than Formulas ...if only I would take the time to learn!" put me in mind of a jet fighter pilot who needed to learn how to drive a car.

And of course you're right - I was so intent on the basic functionality I completely forgot the OP's worksheet set-up. Well, there is one reason I set up the vlookups in the worksheet because it is a simple matter to alter them than (for me) to alter VBA code.

I simply added and then hid the extra columns and expanded the table reference in the vlookup. If you notice the numbers 1-8 in my first screenshot - those refer to table column numbers which are referred to in the vlookup. I changed 1-8 to 1, 3, 5, 7, 9, 11, 13, 14 and it picked up the columns I needed.

In the Macro I changed the line

Range("J1") = Target

to

Range("P1") = Target

I'm sure there is a smarter way to do that so that the cell doesn't have to be hard coded in.

Here's how it looks in action

Video

Posted 2 years ago
Top
 
nosparks
Posts: 148

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

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

Thanks and most importantly - it was fun to do.

Posted 2 years ago
Top
 
Enthusiast
Enthusiast
Posts: 566

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.

Posted 2 years ago
Top
 
amrinderminhas
Posts: 59

Thanx to all,
I am close to the solution but still the problem to how to apply macro persists....(here its worth to mention that i am a mechanical engineer, so doesnt know much about these languages and macros and VBA etc.)

@moreeg
Sir. the first post from you is being followed by me line by line. and i was able to do it upto

' =VLOOKUP($J$1,$A$2:$H$28,J2,FALSE)
And copy that across to Q4 '

But after that the macro code u told me to insert, i am not able to do that exactly. Can u tell me the step by step procedure of how to apply macro code (with those hidden columns being there)

The video you have linked here is exactly what i want.....

@enthusiast
U definitely have always given me not only the answer but also explained me the detail procedure to how to apply these in excel file. But with due respect sir, Due to my weak knowledge in such subjects i was not able to follow the detailed procedure u hv given this time just at the previous post, or just to say that tell me to how to apply macro here so that the moreeg first post works out, It would be helpful..(though i m not doubting ur answer at all, its just my amatureness that i am not able to recieve answer properly)

Posted 2 years ago
Top
 
Enthusiast
Enthusiast
Posts: 566

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.

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

Hi

Nice to see things progress while I slept. Enthusiast, thanks for for the clarifications and improvements. The only thing I would add is about the order of data points in the chart; the functionality to do this is in the chart itself where you can "reverse" the order in the Horizontal Axis properties. You may notice that my example charts are the reverse of Amrinder's and I did that using the chart function.

Also, the LastCol function in the macro seemed to work for me in shading all the columns but I must admit that it surprised me that it worked because of the empty hidden columns. I'll assume there is some peculiarity in my worksheet that allows it so I've changed my macro to match yours.

Amrinder, You'll need to follow the directions I gave with my first video because that takes into account your hidden cells whereas my first post didn't. Although I would encourage you to try to follow Enthusiast's directions which use a much more structured and stable approach.

Also, for applying the Macro follow Enthusiast's directions, there is really no way to make it clearer than that. I have improved on the macro a little (the challenge I always set myself is to see how many fewer lines of code will do the same job).

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

LastRow = Range("A1").End(xlDown).Row 'Finds the last row number containing a Roll No

'Only do this 1. in Column A 2. for populated rows and 3. if only a single selection is made
If Target.Column = 1 And Target.Row > 1 And Target.Row <= LastRow And Target.Cells.Count = 1 Then

'Places the Roll No selected into P1 which contains the reference for the VLOOKUP
Range("P1") = Target

'Unshade all rows and then shade only the selected row
Range("A2", Range("A1").Offset(LastRow, 13)).Interior.TintAndShade = 0
Range(Target, Target.Offset(0, 13)).Interior.TintAndShade = -0.149998474074526
End If

End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Finally, you may find it beneficial to add an Average row as the first row in your table. Here is another video with Average and Median stats added. This being an academic related exercise you may want to add standard deviations or other more sophisticated stuff.

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

Hi Amrinder, Enthusiast and nosparks

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


Download

This will certainly be better than my explanations.

Posted 2 years ago
Top
 
warlock
warlock
Posts: 4100

@moregg, I have nothing to do with this thread but the download worked great.

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

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.

Posted 2 years ago
Top
 
warlock
warlock
Posts: 4100

I think the way you put the last post in is the way to go.

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

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.

Posted 2 years ago
Top
 
warlock
warlock
Posts: 4100

http://www.howtogeek.com/sites.....#38;q=jing I don't know typed in Jing this showed up.

Posted 2 years ago
Top
 
nosparks
Posts: 148

@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

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

The fist one is where I first learned about Jing and Screencast but it says nothing about enabling people to download what you've uploaded. Thinking about it now it's fairly obvious that this should be possible - just never thought about it hard enough.

@Nosparks - yes I do enjoy it as much as I enjoy learning new stuff from yourself and Enthusiast.

Posted 2 years ago
Top
 



Topic Closed

This topic has been closed to new replies.