This must be easy. Pre-set drop down list

paulo78

New Member
Joined
Feb 23, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
Hello All,

I am looking for an easy way to do this and I can not believe that i can not find anything on the internet. Practically i have a simple JobList where every day users enter the jobs/tasks they need to do and then in a dropdown-list they select the percentage of progress for that specific job/task.
Selection is in that drop-down list are 0%;25%;50%;75%;100%. If the task is done then in the cell next to progress cell they choose from a different drop-down-list a checkmark symbol. To mark the job as done. I need the check mark for my reporting later etc.

All this works fine. Now i just simply want that when the user puts the checkmark, then in the cell before with the progress percentage it should show 100%.
This would help the user when they put the checkmark not also have to go additionally and select 100% from the previous drop down cell. Is kind of logic that if he puts the checkmark that means the task is 100%.

Is there anyway to do this ?

Regards
Paulo
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
78
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Your dropdown-list is created with "Data validation" or with Form controls or maybe even with AxtiveX component?
We don't know what your CheckMark is.
Where it is?
Value in cell or Form controls object or maybe even activeX component?

More information is needed to provide any guidance. Thanks
 

paulo78

New Member
Joined
Feb 23, 2021
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
Your dropdown-list is created with "Data validation" or with Form controls or maybe even with AxtiveX component?
We don't know what your CheckMark is.
Where it is?
Value in cell or Form controls object or maybe even activeX component?

More information is needed to provide any guidance. Thanks
Hello,

thank you for your reply and apologies to have not specified the details you mentioned.
The Dropdown- list is made with Data Validation referring to a row of cells on a different sheet. The check mark drop-down list is also a data validation referring to a cell with simply the keyboard symbol “check mark” could also be a asterisk or a exclamation mark. Nothing special.
I hope this helps.
I am willing to change the above mentioned constellation I am still doing trials on this workbook
 

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
78
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Without VBA macros, it probably cat't be modified directly. Dropdown-list created with "Data validation" uses pure values.
You can create a Dropdown list which gives the only option to select 100% if CheckMark is selected, but it cannot change the value already selected.
So it is useless way for your problem.

The value is changeable at least by using VBA Worksheet_Change event but then you need to enable macros in the workbook.
---Just an example---
VBA Code:
Private Sub Worksheet_Change (ByVal Target as Range)
     'Your code
End Sub

Without VBA macros:
However, you can implement it in two parts. (This does not change the value itself, as you want.)
-Use the list to create the contents of the Dropdown menu. -Done-
-Put the cell text in the Dropdown menu in the same color as the cell background. -not needed, only if it's confusing-
-Add a formula to the next cell that takes its value from the Dropdown menu cell except when CheckMark is selected.
excample: =IF(L1="*","100%";J1)
-Users only see the value that comes cell next to the menu.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,780
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top