Worksheet Change Event

Hi,

I have a problem to resolve such as described below:

I have a Workbook which contain many Worksheets and only two of them are needed in the problem I am having. One Worksheet is named "Issues" and the other one is named "Dashboard". My objective is to update the Dashboard Worksheet each time an update is made to the Issue. The field I need to update the dashboard are: the issue number e.g. "IN001" and the status of that issue whether it is "Opened", "Accepted", "Deferred", "Info Req", "Denied" or "Closed".

So, The Dashboard Range for the Issue # is L30 to L38 and the Issue Status range is M30 to M38.

The issue number located in the "Issues" Worksheet is located in Column A starting from the Row 7 onward. and the status of the Issue is located in column N and starting from Row 7. Therefore Issue # A7 to A... and Status will be at N7 to N..

I wish to update the Dashboard only when the Issue Status is Open.

I am not that knowledgeable to write this macro and therefore require your assistance.

Thank you in advance!

Chuck

I am trying to understand what you want to do, and am confused by one thing:

Originally Posted by chamdan

The field I need to update the dashboard are: ...the status of that issue whether it is "Opened", "Accepted", "Deferred", "Info Req", "Denied" or "Closed".

but

I wish to update the Dashboard only when the Issue Status is Open.

I am also not 100% sure a macro is required for this.

Under what conditions is an Issue added, updated in, or removed from the Dashboard?

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Hi Dave,

Ok! here enclosed is the attachment. Please, if you have any question? let me know.

Regards,

Originally Posted by chamdan

Dave,

Someone gave me this solution but unfortunately the update does not work...............Should the issue status changes from Open to any other status the update is not done on the Dashboard. Besides, I wish the 9 oldest Open Issues to be listed only.

Thanks in advance!

I don't see the solution offered in any of the posts. Have you posted this question in other forums?, if so please supply a link to the other posts and read the rules on cross posting.

try this code pasted in the module of Issues sheet
it was based on the workbook lay out that you posted

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, k As Long, n As Long
Dim myRange As Range, cell As Range


LR = Range("N" & Rows.Count).End(xlUp).Row
If LR < 7 Or Target.Column <> 14 Or Target.Count > 1 Then Exit Sub

Set myRange = Range("N7:N" & LR)
Sheets("Dashboard").Range("L30:M38").ClearContents

k = 1
n = 1
For Each cell In myRange
        If k > 9 Or k > LR Then Exit Sub
        If cell.Value = "Open" Then
            k = k + 1
Sheets("Dashboard").Range("L" & 29 + n) = Range("A" & cell.Row).Value
Sheets("Dashboard").Range("M" & 29 + n) = Range("N" & cell.Row).Value
            n = n + 1
        End If
Next cell

End Sub