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 5 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 5 years ago
Top
 
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 5 years ago
Top
 
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 5 years ago
Top
 
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 5 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 5 years ago
Top
 
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 5 years ago
Top
 
nosparks
Posts: 148

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

Posted 5 years ago
Top
 
moreeg
Posts: 842

Thanks and most importantly - it was fun to do.

Posted 5 years ago
Top
 
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 5 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 5 years ago
Top
 
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 5 years ago
Top
 
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 5 years ago
Top
 
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 5 years ago
Top
 
warlock
Posts: 4100

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

Posted 5 years ago
Top
 
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 5 years ago
Top
 
warlock
Posts: 4100

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

Posted 5 years ago
Top
 
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 5 years ago
Top
 
warlock
Posts: 4100

https://www.howtogeek.com/sitesearch/?cx=009481737823548119489%3Aw5lekpyovpo&cof=FORID%3A9&q=jing I don't know typed in Jing this showed up.

Posted 5 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 5 years ago
Top
 
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 5 years ago
Top
 
warlock
Posts: 4100

You are amazing.

Posted 5 years ago
Top
 
amrinderminhas
Posts: 59

@moreeg
Perfect!!!!! the excel file u uploaded is really the best solution more than thousand words.
I have read the programming done and everything else. Though i dont understand those languages but it just give the idea of how to manipulate things.
I have added another cell and then going by same rules i am able to add that cell values in chart (its something fascinating for an amateur like me)

Moreover again understanding a little language i am able to give shade to that added column.
' The only little more help that i needed is that if i would like to have shade of my choice other than the dark shade then what should be done? (though i think it ll be Interior.TintAndShade = -0.14999847407452 to be changed, lol)....isnt it? '

@sparks @Enthusiast
I am really fan of urs as u always try to help me as much as u could.
The three of u make me so confident that i could now take up any challenge of solving the so called difficult problems.....

Posted 5 years ago
Top
 
Enthusiast
Posts: 566

Good to hear amrinderminhas and as always, glad to be of service.

@moreeg, as is usually the case, our government entity has seen fit to exclude screencast from our available sites. Minor imposition for me, since I can follow what you are doing, however, if I am not at home, I cannot upload solutions either. As you said, this will be a useful addition to the tools available for helping those in need.

Posted 5 years ago
Top
 
amrinderminhas
Posts: 59

actually i just want one more answer which i have asked previous to this post that how could i give the shade of my own choice to the row i select....??
The shade that moreeg has given is dark brey in colour.....

Can any of u give me the solution before i close this knowledgeable topic.........

Posted 5 years ago
Top
 
Enthusiast
Posts: 566

amrinderminhas,

Change the following line in the macro for the color you desire:

Change this:

Range(Target, Target.Offset(0, 13)).Interior.TintAndShade = -0.149998474074526

To one of these:

Range(Target, Target.Offset(0, 13)).Interior.Color = 65535 'Yellow Highlight

Range(Target, Target.Offset(0, 13)).Interior.Color = 255 'Red Highlight

Range(Target, Target.Offset(0, 13)).Interior.Color = 15773696 'Blue Highlight

Range(Target, Target.Offset(0, 13)).Interior.Color = 5287936 'Green Highlight

If these colors are insufficient, let us know and we will work with you to get whaqt you want.

Posted 5 years ago
Top
 
moreeg
Posts: 842

Hi Amrinder

Here are 2 other ways to select your own highlighting colours .....

Take a look at this web page for a complete set of colours and their codes. So if you decide to choose the yellow, for example, in the chart it is identified as [color 6]. You would then change the VBA code to....

Range("A2", Range("A1").Offset(LastRow, 13)).Interior.ColorIndex = 0
Range(Target, Target.Offset(0, 13)).Interior.ColorIndex = 6

Another way is to let Excel generate the code you need based on the shading you choose. You would do this with the "Macro Recorder".

Select a cell in an unused part of your worksheet or a blank worksheet and then go to View/Macros and click on the little arrow under Macros and select "Record Macro"
A pop-up will appear telling you the Macro name it is assigning (Macro1 if it is the first one 2 if second and so forth). Make a note of the macro name and click on OKAY
Change the highlight of a cell. If you are not happy with that go to the next cell and try another highlight. When you are done go back to View/Macros and click on the little arrow under Macros and select "Stop Recording"
Finally click on the little arrow under Macros again and select "View Macros". Highlight the macro you just created (remember I topls you to make a note of the assigned macro name?) and then click "Edit" on the right.

You will see something like this

Sub Macro7()
'
' Macro7 Macro
'

'
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("P21").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("P22").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=4
Range("P23").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=-2
End Sub

From here it is just a bit of work to take the code generated for the colour you want and transpose that to the main macro. You'll need to be a bit creative or just come back and ask us.

However, in general, this is a great way to begin constructing your own VBA code. Start the recorder, do the things you want to automate, stop the recorder and then you'll have a good basis for what you want.

Accepted Answer · Posted 5 years ago
Top
 
amrinderminhas
Posts: 59

thanx for the help and tutorial to solve this problem.
I really admire ur social work doing to the community. We have a lot of ideas and thoughts to make work smooth and quick but do not have the skills to make it real.... There comes the real heroes like u.......thanx to all once again......
C u Again...
Take care
God Bless You...

Posted 5 years ago
Top
 



Topic Closed

This topic has been closed to new replies.