asked on

Excel Help

ok, scenario:
I have an excel sheet for a school that has all the students names down the lefts side. and as a header row has all the reasons they could possibly receive a demerit in class. on the las column is the total which is using some conditional format to turn yellow at 2 demerits and Red at 3. What I want to do is have another sheet(s) that auto populates from the first sheet each students detention information, the total demerits and what they were in. I just need to know how to format those cells.

byundt

Could you please post a sample workbook (one or two students with fake names are sufficient) and indicate what information needs to be brought over to the second sheet and how it needs to be formatted. We will suggest formulas, named ranges and conditional formatting as required.

Also, which version of Excel are you using? It makes a difference for conditional formatting.

AlfonsoPina

ASKER

I have come up with some of it already. I have the first sheet basically as an overview. This is the "live" sheet that the students see on the screen all day. the sheets following that are the individuals data and that's where I'm wanting to print out a detention from. Just keep in mind, this is just a rough example. there are more categories but the limits are the same, 3 and you serve! now something I need to mention is that the detention can be served during your lunch or after school. most serve after school. Ocassionaly, since the students don't attend school on Friday except to get additional help to catch up, some teachers opt to have their detentions served on Friday. This is cool because the student thinks he/she is getting a day off and WHAMO! gotta come to school. so maybe that helps describe where I'm going......maybe I should just write all this in Java, lol.
Book1.xlsx

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AlfonsoPina

ASKER

This was way beyond what I thought could be done. I'm fairly decent with basic excel functions, I am gonna go teach myself some macro's now. Thanks, I'm going to start implementing this into the data I already have.....or just rewrite it with this template.

byundt

As written, the macro will use any worksheet named Template. If you prefer your own format to the one I used, just make sure it has the formula in cell A1 (or wherever) to reiterate the student's name.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)
The other formulas in the Template worksheet will then bring back the data from the Detentions worksheet.

I thought you might like to have a hyperlink added to the detail sheet, so I modified the macro to add one automatically. :-)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ws As Worksheet, wsTemplate As Worksheet
Dim targ As Range
Set targ = Range("A2:A1000")    'Watch these cells for double-clicks
Set targ = Intersect(targ, Target)
If targ Is Nothing Then Exit Sub

Set wsTemplate = Worksheets("Template")
On Error Resume Next
Set ws = Worksheets(targ.Value)
On Error GoTo 0
If (ws Is Nothing) And (targ <> "") Then
    wsTemplate.Copy After:=Worksheets(Worksheets.Count - 1)
    Set ws = ActiveSheet
    ws.Name = targ
    Cancel = True
    targ.Worksheet.Hyperlinks.Add Anchor:=targ, Address:="", SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=targ.Value
End If
Application.Goto targ
End Sub

Open in new window

Brad
DetentionsQ28125414.xlsm