Solved

Excel Help

Posted on 2013-05-11
5
196 Views
Last Modified: 2013-05-22
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.
0
Comment
Question by:AlfonsoPina
  • 3
  • 2
5 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39158997
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.
0
 

Author Comment

by:AlfonsoPina
ID: 39185603
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
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39185849
I wrote a macro to create a detention worksheet for a student. The format is copied from a worksheet Template that already contains the right formulas. The new worksheet will carry the student's name as its name. The macro is triggered by double-clicking the student's name on the Detention worksheet.

This macro is installed in the code pane for the Detention worksheet, and won't work at all if installed anywhere else. Right-click the sheet tab and choose View Code... to see it.

Note that you will get a macro warning when you open the workbook. You must enable macros if you want the double-click to create a new worksheet trick to work. You don't need macros for normal operation (adding demerits to the list).
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
End If
Application.Goto targ
End Sub

Open in new window

The formula I used on the detail worksheet to retrieve the values is:
=VLOOKUP($A$1,detention!$A$2:$H$100,MATCH($A2,detention!$A$1:$G$1,0),FALSE)
This formula may be copied down as required. It assumes that the text in cell A2 will match one of the column headers from Detention worksheet A1:G1. Because you had "Total" on the Detention worksheet and "Total Number of Demerits" on the detail worksheets, I used a Custom format so cell A2 contains "Total" but displays "Total Number of Demerits:"

Brad
DetentionsQ28125414.xlsm
0
 

Author Closing Comment

by:AlfonsoPina
ID: 39189179
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.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39189282
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel split data and output all combinations. 2 46
Macro 6 60
Create Form using Wizard 14 44
multiple columns far out to BG need to transpose 6 18
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Outlook Free & Paid Tools
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question