Worksheet Change Event

[chamdan]

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

[6StringJazzer]

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

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?

[chamdan]

Hi 6StringJazzer,

Sorry for the confusion. The objective is to update the dashboard where the issues that are open will be listed until they change status and then will be removed from the dashboard. The Cells addresses in the Dashboard start at L30:L38, which will contain the issue number and corresponding to the issue number the status here: "open" will be listed at the address M30:M38. Hope this clarifies my request?

Each time an issue is opened in the worksheet named "Issues" a number will be entered in column A and a status will be selected from a Data Validation list found in the Issues Worksheet at the address in column N.

[davesexcel]

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!

[chamdan]

Hi Dave,

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

Regards,

[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!

[davesexcel]

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.

[Osvaldo Palmeiro]

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

Please Login or Register  to view this content.

[chamdan]

The code you gave me works perfectly the way I was expecting.

Thanks a lot!