How to count the no. of times a specific value is selected in a cell

[rizwanulhasan]

Hi all,

In the attached excel file, i have the below requirement:

Column D of sheet "DM PR Availability" contains a dropdown for selecting Do Not Disturb. Whenever a person selects Do Not Disturb, i want the no. of count to be displayed in the DM Dashboard sheet (accessible through Dashboard sheet) under column M for that respective ID. Please note that the count should be displayed only when Do Not Disturb is selected and not when that cell is deleted.

Note: Sheets : "DM Records", "DM PR Availability" and "Dashboard" will be in Protected Sheet and Protected Workbook state.

Can anyone help with my requirement. Many thanks in advance.

[FDibbins]

As you probably know, thuis will require VBA (not formulas), so I will move your thread to that forum.

[maniacb]

Lines have been added to the worksheet_change code on the "DM PR Availability" sheet code highlighted in red below. Let us know if this works for you.

Please Login or Register  to view this content.

[rizwanulhasan]

Hi maniacb,

Thank you so much for the solution. Its working perfectly.

However, i had one more requirement.

In the attached file, whenever, the cell in D column of DM PR Availability sheet is set to "Do Not Disturb", i want its time stamp (dd-mmm-yyyy hh:mm AM/PM) to be captured in the DND Timestamps sheet against the respective ID.

All the timestamps should be recorded one below another against the respective IDs.

Could you please see if its possible. Many thanks in advance.

[maniacb]

I was able to accomplish your requirement only by reverting the table in DND Timestamps back to a regular range and moving the count total above the timestamp columns. See if that works for you.

Please Login or Register  to view this content.

[rizwanulhasan]

All perfect maniacb. Thank you once again.

However, i required two blank rows (Row 2 and 3) in DND Timestamps sheet. Now, one of the row has been occupied with the count total.

I tried inserting a blank row, but that seems to have disturbed the VBA.

Any alternative through which i can insert a row above count?

[maniacb]

Update the following line of code as follows:

Please Login or Register  to view this content.

[rizwanulhasan]

Thanks a ton maniacb. You really saved my day.

[rizwanulhasan]

Hi maniacb,

Just a minor query.

In case i lock the "DND Timestamps" sheet, then the VBA displays an error while capturing the timestamp.

Any solution so that the timestamps should be captured even if the "DND Timestamps" sheet is locked.

Thanks again in advance.

[maniacb]

You can use Protect and unprotect lines with the appropriate password like below.

Please Login or Register  to view this content.

[rizwanulhasan]

Thank you maniacb.

1 more query - I am sure you might be able to resolve it.

If you see the sheet "DM Records", it is protected with password 123. However few cells are unlocked i.e. C3:C32

Now even though the cells are unlocked but when i try to enter data in these cells, excel asks for a password. where in fact it shouldn't ask because the cells are unlocked.

Can you help with this issue. Thanks in advance.

[maniacb]

You need to unprotect a range in the protected sheet. I made the changes but here are the instructions. https://support.microsoft.com/en-us/...3-042a5f2cd93a

[rizwanulhasan]

Hi maniacb,

Thank you for your response.

I tried the method provided in the link to unlock ranges but couldn't succeed.

Also in the file you shared, when i try entering anything in column C, excel displays a password popup.

It only works fine if the sheet is protected without a password. Here i entered 123 as password and the pop up is displayed even when entering data in unlocked range..

Is there anything to be changed in VBA as you prescribed earlier

dnd.Unprotect Password:="Password"
dnd.Protect Password:="Password"

Thanks again.