How do change a drop down value based on any cell value

[4lowie]

How do change a drop down value based on any cell value?

I have a simple spreadsheet where I need to change a cell value from a drop down when I double click on another cell to enter todays date.
There is a macro that copies a line and inserts it down, so its a list of rows top down. In column A I have a drop down selection, "Open","In Progress" and "Closed, when I double click in column L on the same line it inserts todays date, at that time I'd like to update the value in column A to show "Closed".

The line I copy and insert down is row 3 so I suppose thats where the formula should site...

When I double click on the "Closed Date" cell I'd like the "Action Status" drop down to update to show closed.
The 3 date cells can be updated by double clicking to enter todays date.

I also had a question around when a new line is created via my copy and insert down macro, I wanted the "Date Raised" cell to show todays date... Trouble is it always had todays date, not the date the line was created!

Thanks again for looking at it for me, file attached. The PW is 0 (zero)

Dave

Dave's Action Register.xlsm

[stimulus]

Hi Dave,

Please see the attached.
I did a couple of things in there, one of them was to add the code to set the progress as closed when you double click the closed date cell in a row.
The date raised will populate the current date when a new action is created, and the last was around the data validation.

If you check out the name manager and the drop downs sheet, you will see that the lists for each list has been defined using the offset function along with a Counta to define the number of rows in the list. So if you add more items to any of the lists or take some away, your list will automatically adjust it's size accordingly.

Hope this helps.

Thanks,
James.


Dave's Action Register.xlsm

[4lowie]

Fantastic! Thanks very much for your help. Much appreciated.

Dave