Hello and thank you for your time...Here is my scenario. I have created an
audit in one worksheet of Excel 2002.

The audit in worksheet A contains a drop down list to display each of the
ratings (i.e. meets expectations, needs improvement, etc) and another drop
down list to display a list of my 10 employees.

What I would like to know is, how do I create a report from worksheet A in
lets say worksheet B, to reflect each individual employee and their ratings
everytime I choose an employee from the drop down list and keep some kind of
a rolling report whenever I choose a certain employee on the same audit form?

I would be willing to email someone my report for more clarity on this issue.

Thanks in advance!

One method would be to use a worksheet change function. Then when the cell
is changed copy the data to the fisrt empty row on a summary worksheet.

to add code, right click tab at bottom of worksheet( normally sheet1).
Select view code. Paste code below on this sheet. Worksheet change
functions are place in VBA sheets pages (not module) and must be put on each
sheet you want the code to run on.


Sub worksheet_change(ByVal Target As Range)

For Each cell In Target
If (cell.Row = 5) And (cell.Column = 6) Then
EmptyRow = Sheets("Summary"). _
Cells(Rows.Count, "A").End(xlUp).Row
If (EmptyRow = 1) And _
IsEmpty(Sheets("Summary").Cells(1, "A")) Then
EmptyRow = 1
Else
EmptyRow = EmptyRow + 1
End If

cell.EntireRow.Copy Destination:= _
Sheets("Summary").Rows(EmptyRow & ":" & EmptyRow)
Exit For
End If
Next cell

End Sub


"Somewhere In Excel 2002" wrote:

Hello and thank you for your time...Here is my scenario. I have created an
audit in one worksheet of Excel 2002.

The audit in worksheet A contains a drop down list to display each of the
ratings (i.e. meets expectations, needs improvement, etc) and another drop
down list to display a list of my 10 employees.

What I would like to know is, how do I create a report from worksheet A in
lets say worksheet B, to reflect each individual employee and their ratings
everytime I choose an employee from the drop down list and keep some kind of
a rolling report whenever I choose a certain employee on the same audit form?

I would be willing to email someone my report for more clarity on this issue.

Thanks in advance!

Hi Joel,
Would you mind if I sent you my spreadsheet to show you what I am trying to
do?


Thanks

"Joel" wrote:

One method would be to use a worksheet change function. Then when the cell
is changed copy the data to the fisrt empty row on a summary worksheet.

to add code, right click tab at bottom of worksheet( normally sheet1).
Select view code. Paste code below on this sheet. Worksheet change
functions are place in VBA sheets pages (not module) and must be put on each
sheet you want the code to run on.


Sub worksheet_change(ByVal Target As Range)

For Each cell In Target
If (cell.Row = 5) And (cell.Column = 6) Then
EmptyRow = Sheets("Summary"). _
Cells(Rows.Count, "A").End(xlUp).Row
If (EmptyRow = 1) And _
IsEmpty(Sheets("Summary").Cells(1, "A")) Then
EmptyRow = 1
Else
EmptyRow = EmptyRow + 1
End If

cell.EntireRow.Copy Destination:= _
Sheets("Summary").Rows(EmptyRow & ":" & EmptyRow)
Exit For
End If
Next cell

End Sub


"Somewhere In Excel 2002" wrote:

Hello and thank you for your time...Here is my scenario. I have created an
audit in one worksheet of Excel 2002.

The audit in worksheet A contains a drop down list to display each of the
ratings (i.e. meets expectations, needs improvement, etc) and another drop
down list to display a list of my 10 employees.

What I would like to know is, how do I create a report from worksheet A in
lets say worksheet B, to reflect each individual employee and their ratings
everytime I choose an employee from the drop down list and keep some kind of
a rolling report whenever I choose a certain employee on the same audit form?

I would be willing to email someone my report for more clarity on this issue.

Thanks in advance!

I've given my e-mail address to lots of peoople, one more can't hurt.



"Somewhere In Excel 2002" wrote:

Hi Joel,
Would you mind if I sent you my spreadsheet to show you what I am trying to
do?


Thanks

"Joel" wrote:

One method would be to use a worksheet change function. Then when the cell
is changed copy the data to the fisrt empty row on a summary worksheet.

to add code, right click tab at bottom of worksheet( normally sheet1).
Select view code. Paste code below on this sheet. Worksheet change
functions are place in VBA sheets pages (not module) and must be put on each
sheet you want the code to run on.


Sub worksheet_change(ByVal Target As Range)

For Each cell In Target
If (cell.Row = 5) And (cell.Column = 6) Then
EmptyRow = Sheets("Summary"). _
Cells(Rows.Count, "A").End(xlUp).Row
If (EmptyRow = 1) And _
IsEmpty(Sheets("Summary").Cells(1, "A")) Then
EmptyRow = 1
Else
EmptyRow = EmptyRow + 1
End If

cell.EntireRow.Copy Destination:= _
Sheets("Summary").Rows(EmptyRow & ":" & EmptyRow)
Exit For
End If
Next cell

End Sub


"Somewhere In Excel 2002" wrote:

Hello and thank you for your time...Here is my scenario. I have created an
audit in one worksheet of Excel 2002.

The audit in worksheet A contains a drop down list to display each of the
ratings (i.e. meets expectations, needs improvement, etc) and another drop
down list to display a list of my 10 employees.

What I would like to know is, how do I create a report from worksheet A in
lets say worksheet B, to reflect each individual employee and their ratings
everytime I choose an employee from the drop down list and keep some kind of
a rolling report whenever I choose a certain employee on the same audit form?

I would be willing to email someone my report for more clarity on this issue.

Thanks in advance!

Thanks Joel, I just sent my example spreadsheet

"Joel" wrote:

I've given my e-mail address to lots of peoople, one more can't hurt.



"Somewhere In Excel 2002" wrote:

Hi Joel,
Would you mind if I sent you my spreadsheet to show you what I am trying to
do?


Thanks

"Joel" wrote:

One method would be to use a worksheet change function. Then when the cell
is changed copy the data to the fisrt empty row on a summary worksheet.

to add code, right click tab at bottom of worksheet( normally sheet1).
Select view code. Paste code below on this sheet. Worksheet change
functions are place in VBA sheets pages (not module) and must be put on each
sheet you want the code to run on.


Sub worksheet_change(ByVal Target As Range)

For Each cell In Target
If (cell.Row = 5) And (cell.Column = 6) Then
EmptyRow = Sheets("Summary"). _
Cells(Rows.Count, "A").End(xlUp).Row
If (EmptyRow = 1) And _
IsEmpty(Sheets("Summary").Cells(1, "A")) Then
EmptyRow = 1
Else
EmptyRow = EmptyRow + 1
End If

cell.EntireRow.Copy Destination:= _
Sheets("Summary").Rows(EmptyRow & ":" & EmptyRow)
Exit For
End If
Next cell

End Sub


"Somewhere In Excel 2002" wrote:

Hello and thank you for your time...Here is my scenario. I have created an
audit in one worksheet of Excel 2002.

The audit in worksheet A contains a drop down list to display each of the
ratings (i.e. meets expectations, needs improvement, etc) and another drop
down list to display a list of my 10 employees.

What I would like to know is, how do I create a report from worksheet A in
lets say worksheet B, to reflect each individual employee and their ratings
everytime I choose an employee from the drop down list and keep some kind of
a rolling report whenever I choose a certain employee on the same audit form?

I would be willing to email someone my report for more clarity on this issue.

Thanks in advance!