Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel cell to display summary of columns
I have a Excel sheet that has 71 rows and 140 columns. In the colums are
training courses the employees have taken and in the rows are my employees. I want to be able to click on the cell that has an employees name and a window to pop up with the summary of the courses they have taken.....like the comment box. I do not want to make a comment and have to edit each comment when an employee completes a training course. I want the cell pop up window with the employees name to automatically know and list the courses taken, to pull it from the spreadsheet. Any ideas? I am not sure if this is even possible. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel cell to display summary of columns
Danielle,
In the data, are there X's or something where the names and courses meet? For example: Name Course 1 Course 2 Course 3 Name1 x Name2 x x Name3 x x -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Danielle" wrote: I have a Excel sheet that has 71 rows and 140 columns. In the colums are training courses the employees have taken and in the rows are my employees. I want to be able to click on the cell that has an employees name and a window to pop up with the summary of the courses they have taken.....like the comment box. I do not want to make a comment and have to edit each comment when an employee completes a training course. I want the cell pop up window with the employees name to automatically know and list the courses taken, to pull it from the spreadsheet. Any ideas? I am not sure if this is even possible. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel cell to display summary of columns
Exactly.
"Thomas [PBD]" wrote: Danielle, In the data, are there X's or something where the names and courses meet? For example: Name Course 1 Course 2 Course 3 Name1 x Name2 x x Name3 x x -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Danielle" wrote: I have a Excel sheet that has 71 rows and 140 columns. In the colums are training courses the employees have taken and in the rows are my employees. I want to be able to click on the cell that has an employees name and a window to pop up with the summary of the courses they have taken.....like the comment box. I do not want to make a comment and have to edit each comment when an employee completes a training course. I want the cell pop up window with the employees name to automatically know and list the courses taken, to pull it from the spreadsheet. Any ideas? I am not sure if this is even possible. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel cell to display summary of columns
Alright Danielle, try this:
Sub Add_Course_Comment() Dim CommentValue As String, sPayer As String, cell As Range Dim wks1 As Worksheet Dim sReportName As String Dim i As Integer, iRow As Long Dim c As Object '' WHENEVER THERE'S A COLUMN OR ROW CHANGE TO THIS REPORT, THESE CONSTANTS HAVE TO CHANGE Const cStartRange = 2 Const cEndRange = 140 Const rStartRange = 2 Const rEndRange = 71 Set wks1 = ActiveSheet With wks1 For iRow = rStartRange To rEndRange If Range("A" & iRow).Value = "" Then GoTo EndRows Else For i = cStartRange To cEndRange If Range("A1").Cells(iRow, i).Value < "" Then sPayer = CStr(Range("A1").Cells(1, i).Value) Else sPayer = "" End If If sPayer < "" Then If i = cStartRange Then CommentValue = sPayer Else If CommentValue = "" Then CommentValue = sPayer Else CommentValue = CommentValue & Chr(10) & sPayer End If End If End If Next i End If If CommentValue = "" Then CommentValue = "No Courses Taken" End If Range(Cells(iRow, 1), _ Cells(iRow, 1)).NoteText _ Text:=CommentValue CommentValue = "" Next iRow EndRows: End With End Sub -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Danielle" wrote: Exactly. "Thomas [PBD]" wrote: Danielle, In the data, are there X's or something where the names and courses meet? For example: Name Course 1 Course 2 Course 3 Name1 x Name2 x x Name3 x x -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Danielle" wrote: I have a Excel sheet that has 71 rows and 140 columns. In the colums are training courses the employees have taken and in the rows are my employees. I want to be able to click on the cell that has an employees name and a window to pop up with the summary of the courses they have taken.....like the comment box. I do not want to make a comment and have to edit each comment when an employee completes a training course. I want the cell pop up window with the employees name to automatically know and list the courses taken, to pull it from the spreadsheet. Any ideas? I am not sure if this is even possible. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel cell to display summary of columns
Thomas,
Thanks for the info. It works except it only lists my header in the pop up window. My employees start in row A6 and my course info starts in B3. It pulls the header info I have in B1 and places it in the pop up window. Thanks, Danielle "Thomas [PBD]" wrote: Alright Danielle, try this: Sub Add_Course_Comment() Dim CommentValue As String, sPayer As String, cell As Range Dim wks1 As Worksheet Dim sReportName As String Dim i As Integer, iRow As Long Dim c As Object '' WHENEVER THERE'S A COLUMN OR ROW CHANGE TO THIS REPORT, THESE CONSTANTS HAVE TO CHANGE Const cStartRange = 2 Const cEndRange = 140 Const rStartRange = 2 Const rEndRange = 71 Set wks1 = ActiveSheet With wks1 For iRow = rStartRange To rEndRange If Range("A" & iRow).Value = "" Then GoTo EndRows Else For i = cStartRange To cEndRange If Range("A1").Cells(iRow, i).Value < "" Then sPayer = CStr(Range("A1").Cells(1, i).Value) Else sPayer = "" End If If sPayer < "" Then If i = cStartRange Then CommentValue = sPayer Else If CommentValue = "" Then CommentValue = sPayer Else CommentValue = CommentValue & Chr(10) & sPayer End If End If End If Next i End If If CommentValue = "" Then CommentValue = "No Courses Taken" End If Range(Cells(iRow, 1), _ Cells(iRow, 1)).NoteText _ Text:=CommentValue CommentValue = "" Next iRow EndRows: End With End Sub -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Danielle" wrote: Exactly. "Thomas [PBD]" wrote: Danielle, In the data, are there X's or something where the names and courses meet? For example: Name Course 1 Course 2 Course 3 Name1 x Name2 x x Name3 x x -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Danielle" wrote: I have a Excel sheet that has 71 rows and 140 columns. In the colums are training courses the employees have taken and in the rows are my employees. I want to be able to click on the cell that has an employees name and a window to pop up with the summary of the courses they have taken.....like the comment box. I do not want to make a comment and have to edit each comment when an employee completes a training course. I want the cell pop up window with the employees name to automatically know and list the courses taken, to pull it from the spreadsheet. Any ideas? I am not sure if this is even possible. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel cell to display summary of columns
Danielle,
Only semi-confused. I was under the impression that Row 1 were the headers (course names) and starting in row 2 were the employees and the X's. I have changed the coding, which seemed to have worked, however without knowing the exact layout of the table, it is only a guess as to what you would want displayed. Line 12: Const rStartRange = 6 Determines the start row of your employees, I changed it to 6. Line 27: sPayer = CStr(Range("A1").Cells(3, i).Value) Returns the header/course name, I changed the Cells(3,i) part to pull from row 3. If you could make those changes in the code, please do so. If you wish, please place an example of A1:D8 for more clarification on what you would like the comment to show. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Danielle" wrote: Thomas, Thanks for the info. It works except it only lists my header in the pop up window. My employees start in row A6 and my course info starts in B3. It pulls the header info I have in B1 and places it in the pop up window. Thanks, Danielle "Thomas [PBD]" wrote: Alright Danielle, try this: Sub Add_Course_Comment() Dim CommentValue As String, sPayer As String, cell As Range Dim wks1 As Worksheet Dim sReportName As String Dim i As Integer, iRow As Long Dim c As Object '' WHENEVER THERE'S A COLUMN OR ROW CHANGE TO THIS REPORT, THESE CONSTANTS HAVE TO CHANGE Const cStartRange = 2 Const cEndRange = 140 Const rStartRange = 2 Const rEndRange = 71 Set wks1 = ActiveSheet With wks1 For iRow = rStartRange To rEndRange If Range("A" & iRow).Value = "" Then GoTo EndRows Else For i = cStartRange To cEndRange If Range("A1").Cells(iRow, i).Value < "" Then sPayer = CStr(Range("A1").Cells(1, i).Value) Else sPayer = "" End If If sPayer < "" Then If i = cStartRange Then CommentValue = sPayer Else If CommentValue = "" Then CommentValue = sPayer Else CommentValue = CommentValue & Chr(10) & sPayer End If End If End If Next i End If If CommentValue = "" Then CommentValue = "No Courses Taken" End If Range(Cells(iRow, 1), _ Cells(iRow, 1)).NoteText _ Text:=CommentValue CommentValue = "" Next iRow EndRows: End With End Sub -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Danielle" wrote: Exactly. "Thomas [PBD]" wrote: Danielle, In the data, are there X's or something where the names and courses meet? For example: Name Course 1 Course 2 Course 3 Name1 x Name2 x x Name3 x x -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "Danielle" wrote: I have a Excel sheet that has 71 rows and 140 columns. In the colums are training courses the employees have taken and in the rows are my employees. I want to be able to click on the cell that has an employees name and a window to pop up with the summary of the courses they have taken.....like the comment box. I do not want to make a comment and have to edit each comment when an employee completes a training course. I want the cell pop up window with the employees name to automatically know and list the courses taken, to pull it from the spreadsheet. Any ideas? I am not sure if this is even possible. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot table to display the value instead of a summary | Excel Discussion (Misc queries) | |||
Display a weekly summary | Excel Worksheet Functions | |||
selecting different cell ranges across sheets, to display on summary page | Excel Discussion (Misc queries) | |||
How do I display data from multiple worksheets in a summary sheet. | Excel Discussion (Misc queries) | |||
Display summary sheet with Now or Today data. | New Users to Excel |