Log In
Register
Home
Active Member
September 14, 2021
Offline
I am working in the SAPS, I am responsible for managing information. I receive a lot of returns from 35 units, so I use a checklist to monitor my returns received. I have made a checklist with checkboxes to speed up my capturing process, I also used conditional formatting to change the colour of my block green once I select the checkbox. My question is this: can I add a date to my checklist with a formula that will work with my checkbox, the trick is the date must be fixed as I need to monitor dates received, so datestamp functions like today will not work as they update to a new date once you open the document the next day. I added my example with columns A to H as my checklist and columns AF to AM as my hidden data that controls my checkbox form controls.
So I am hoping that I will be able to just click on my checkbox and my block wil go green and the date will be automatically added to the same block that will not update in future.
Is this possible? I am not familiar with VBA so hoping this is not the only solution, your help will be greatly appreciated.
I am Running windows 10 pc with excel 2016
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
If I may, I would recommend that you forget about the check boxes all together. They may look nice but the are not easy to work with if you have many. And it would require VBA or an iterative formula (i.e. one that causes circular references which you then have to allow in the settings) to achieve what you want.
The simplest solution would be to use a keyboard shortcut Shift-Ctrl-; to enter a static date stamp in a cell. Conditional formatting will still work with an =ISNUMBER(B4) rule, applied to $B$4:$H$46.
The attached file demonstrates what I mean. Note that it knocks off more than 100KB from the file size and improves performance.
1 Guest(s)
