Solved

Excel Help

Posted on 2013-05-11
5
191 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 80

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 80

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 80

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now