Make me look like an Excel/VBA Rockstar....

[gregj]

Hello all, This being my first post here I will start off first by saying thank-you to all of you who have learned how to utilize a product in a much more expert way than I......

I am a regular user of excel, and am somewhat skilled in making it do what I want, but know ZERO (0) about the vba side of things. A little introduction first..... I am an education administrator for a school of medicine. I am trying to find a way to solve a grade reporting issue, in that grades are too often not filtered into the system in as timely a fashion as necessary. I need to see the grades of our students at the same time as they are entered into the profs spreadsheets, so that I can identify the students who are in trouble.

What I have done thus far is as follows:

1) created an "original spreadsheet, with conditional formatting so that if an entry is less than 70 the text is colorred red.
2) created a duplicate to this sheet, so that as a grade is entered into one cell, it will reflect on the other seperate sheet in a seperate book.
3) created automatic graphs so as a number is reflected in a cell, an automatic point is assigned on the graph in the corresponding position.

What I really need to have happen is as follows:

1) It would be great that if a cell has an entry that is 75 or less an autmatic email is generated at a point in time later...say 120 mins later, that alerts me to this fact.

2) It would perhaps be even better if the cut n paste method was not the method employed in order to create the spreadsheet so that I can see it.... recall that the original sheet is the profs...(will reside on a shared drive) and the other book hereunto refered as the LAW book... is mine and sits on my computer.....

I am going to attach the two spreadsheets and see if there is anything y'all can suggest to help me in this matter. In advance I appreciate your help....if this is a time consuming issue.....there could be beer money in it for you...... :beerchug: I see also that I cannot add two files... so I will immediately post the second below this....if it will fit, it is larger I guess beciase it is a paste version?

[gregj]

I was not able to upload this other file......it is 1.5MB.... :(
If someone has a suggestion?

[gregj]

ooops let me reattach after removing some info.....my bad...

[gregj]

Anyone who can help out there?

[grichey]

zip your files together

[Aussiebear]

My first impression is that you have an extremly bloated workbook. Why not use just one chart and select your student of interest from a drop down list? Or just apply a pivot table and or a pivot chart?

[gregj]

My first impression is that you have an extremly bloated workbook. Why not use just one chart and select your student of interest from a drop down list? Or just apply a pivot table and or a pivot chart?

I agree it is full, but I am not sure how to do what you are saying with a pivot table.... ?

[Aussiebear]

Here's something that although built in 2007, but available in 2003 format, may give you some ideas as to what is possible. I'm not an expert in charting, and in fact its more than likely that someone will easily improve the chart type and layout.

For the purpose of adding test scores I've used the Randbetween function. In this case Randbetween(60,100). For trialing, simply select the cell and drag across and down to fill the other data cells required.

The chart is located on Sheet5, only because the other sheets were used to experiment with and failed. You can select the student by using the Student Name selector. The name of the Student in cell A3 will change accordingly as will the data in the chart. I've also added a trend line in the chart, which while you only have single test results doesn't do much, its when multiple test results are available that it has an effect.

Somewhere I once read something about having a series of lines in a chart which are not linked, as in 1st four tests (Biology) are represented by one line, 2nd four tests Genetics) by another etc. It would be nice to see someone have a go at that as a possible result.

[gregj]

nope... i got it ... but it won't let me open it... is read only or the server document is stored on is not responding.... I'll try later thanks for looking into this though....

[Aussiebear]

This workbook has developed some issues which I am currently investigating. I wil repost the entire workbook as soon as it is resolved

[Aussiebear]

Right, the xls version is working.

[gregj]

:beerchug: That friggin rocks.... Sure would like to know how to do that. Will it be compatible with other versions of excel. BTW thanks......VERY MUCH.

[gregj]

K, now that I have had a few mins to mess with this I am certainly intrigued by it. I a curious if I can do what I had originally planned which is have a seperate version of the sheets and the slider graph page for professors, and one for me such that as they update their grades my grade sheet and slider bar updates automatically or as I open up the workbook. Additionally I need to protect the sheet so that all a prof can do is enter grades. If they need to use formulas to calculate them, then they can do that on their own sheets, but can only paste values into the (shared drive sheet) - which updates my version as mentioned.

Is there somewhere you can point me to figure this sheet out? This is soooo cool.

[Simon Lloyd]

Gregj, i have removed the solved from this post, when you have completed your solution in this thread please mark it solved again!

[Aussiebear]

There's nothing really special about the workbook. Do a Google search for dynamic charting. The data in sheet1 has been arrived at by using the Rand between function so those cells will need to be cleared and unlocked for use by who ever enters the data. On sheet2 be careful of not deleting the link cell I1. (Its font is White)

[Aussiebear]

I a curious if I can do what I had originally planned which is have a seperate version of the sheets and the slider graph page for professors, and one for me such that as they update their grades my grade sheet and slider bar updates automatically or as I open up the workbook. Additionally I need to protect the sheet so that all a prof can do is enter grades. If they need to use formulas to calculate them, then they can do that on their own sheets, but can only paste values into the (shared drive sheet) - which updates my version as mentioned.

I don't know why you can't do this.

[gregj]

Aussie,
Thanks again for your help. I have been messing around with this and sort of "reverse-engineering" it. I can now make my own scrolly etc. What I can not get to work is this: In the worksheet, where all the students names and grades are, if I clear out data, and replace it with my name and add a system of tracking data such as a bunch of really low scores, the data reflects in the chart, but remains there across all names scrolled for. Does this make sense?

If you look at the attached sheet, you will see that I have messed with John Banahan's data....but this is not reflected in the positioning of the scroll bar. What I am hoping to achieve is that for every time a name is selected using the scroll, the chart indicates that individual students progressive grades across all subject areas.

Do I have to go in and make indiviidual series, attach the proper cell ranges to data points etc....? :banghead:

BTW, I managed to get the data to automatically "link" from one sheet to another....which is what I had originally started with if you recall.... then I had 118 individual charts... <---retarded ] this way is much better. I just hope the community here is still willing to help with this as I am learning a crapload here which is always gratefully appreciated.

[mdmackillop]

Basic error here. The slider change is triggering a change in the random numbers, not the showing the data relating to the student name. Copy/PasteSpecial Values to see what I mean.

[mdmackillop]

Add this to a standard module and assign it to the scrollbar


Sub ChartUpdate()
Dim Rng As String, Rw As Long
Dim cel As Range
Set cel = Selection
Rw = Range("M1").Value + 2
'Modify data range
Rng = "$C$" & Rw & ":$AL$" & Rw

ActiveSheet.ChartObjects("Chart 2").Select
ActiveChart.SeriesCollection(1).Formula = "=SERIES(,,Sheet1!" & Rng & ",1)"
'Return activation to sheet
cel.Activate
End Sub


BTW There is a question on a similar topic here (http://www.vbaexpress.com/forum/showthread.php?t=19929)

[Aussiebear]

Malcolm, I get an error at the Set cel = Selection line. I take it that I stil need to select the data by using the wizard or does the code do this?

[mdmackillop]

That is only to return focus to the sheet.
Delete that line and the cel.Activate line.

[gregj]

I added this to a module...not sure if I did it right... tools macro , vba module, paste close save.....

However it still appears as the same chart as I scroll through it. Where points appear on the 0 level throughout the scroll.

I really appreciate the insight here fellows... sure would like to buy y'all a beer or two.

[lucas]

Greg, did you right click on the slider and select "assign macro" and then pick the ChartUpdate macro?

[gregj]

ok i did that..... (and deleted the two lines mdmckillop mentioned...(see below) .cool now i have to test it out to ensure that it is working properly.....if it is I have found perhaps the best forum on the net for excel.......

next and hopefully final question for this challenge. Can I duplicate this chart and functions into another, with a different name and then simply paste special the grade input section into the new chart to create a link? Will the Scroll work the same, in both books, where grades for person x will erveal in the second workbook if that name is what appears in the scroll selection? Does that make sense?


Appreciate it.....


Sub ChartUpdate()
Dim Rng As String, Rw As Long
Dim cel As Range
Rw = Range("M1").Value + 2
'Modify data range
Rng = "$C$" & Rw & ":$AL$" & Rw
ActiveSheet.ChartObjects("Chart 2").Select
ActiveChart.SeriesCollection(1).Formula = "=SERIES(,,Sheet1!" & Rng & ",1)"
'Return activation to sheet
End Sub



Greg, did you right click on the slider and select "assign macro" and then pick the ChartUpdate macro?

[mdmackillop]

You also need to get rid of the random data by copy/paste special values. Ted's use of it was fine to generate sample data, but it confuses the issue as it changes each time the scrollbar is used.

[Aussiebear]

Try this Greg. I've set the data up as straight values on sheet1, and added the code of Malcolm's

[mdmackillop]

Ted,
I'm getting an error and the file is opening with no data.

[mdmackillop]

Here's my test version

[Aussiebear]

I don't know what I've done to the file...but anyway your version is as good as it gets. Thanks MD

[gregj]

:bow: OK YOU GUYS EXCEL :bow:

I bow in your presence.......


I thought I would never figure this out and in a weekend you all have done so, without fail I will recommend this site for any and all excel challenges. I will go out and buy a VBA book, asap.

THANK YOU SO MUCH.... -- :clap:

[mdmackillop]

I will go out and buy a VBA book, asap.

Great news, Then you can help answer questions!

[gregj]

Well I am not so sure about that, but certainly will try where able.


Great news, Then you can help answer questions!

I am not going mark this as solved just yet as I might want to figure out the idea of an email alert attached to an instance of a 75 or less for any student whereby if a score of 75 is received in one students "grade - row" it emails me to let me know to move them to the naughty list.....must always whip them... if encouragment is not enough. (J/K) :devil2: If it is a doable thingamabob, then that would be cool to figure out (with expert help) of course.

[mdmackillop]

Best to post that as a new question. You can add a link back to this if necessary.