Automatically change a drop-down based on selection of another drop-down

Hello,

I am trying to figure out a way to automatically change a cell value (which has a drop-down validation on it) based on the selection of another drop-down.

I have attached an example of my worksheet.

Worker 1 selects the status of the job and once completed it passes to worker 2.
Worker 2 then selects the status of the job and once complete moves to worker 3 and so on.

However if worker 2 discovers an error and needs to pass the work back to worker 1 they will select 'worker 1 to review'

This is where the automatic change comes in. Once 'worker 1 to review' has been selected, I need the status cell on worker 1 to change from 'ready for worker 2' to 'not started'
Then once it has been reviewed and worker 1 changes the status to 'ready for worker 2' (for the second time) I need the job status of worker 2 to change from 'worker 1 to review' to 'not started'.

Is there any way to do this either through formula/validation or vba?

Thanks for reading.
Kind regards,
Joe

Hi, thanks for your reply.

Yeah i was messing about with macros to try and get it to work, before making an example one to upload here.

Yes, you're right, that is the way it works. However when worker 2 sets it back to worker 1 i need the worker 1 status to change back to 'not started' automatically.

The issue is worker 1 will only be filtering on their specific statuses to track the work that needs doing. Therefore if worker 2 sets their status to 'worker 1 to review', worker 1 will not pick this up as their status will still be set at 'ready for worker 2'

Hi,

Why do you think I already have macros? I don't have any macros. I tried to record a couple of macros but they were completely wrong so i deleted them.

Hi,

Ok thank you that works one way, but how do i replicate this if i have more workers? I have only a very basic understanding of macros.

The logic I've used doesn't seem to be working. Here's the code;


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I3:I6")) Is Nothing Then
If Target.Value = "Worker 1 to review" Then Target.Offset(, -4) = "Not Started"
End If

If Not Intersect(Target, Range("E3:E6")) Is Nothing Then
If Target.Value = "Ready for Worker 2" Then Target.Offset(, 4) = "Not Started"
End If

End Sub

I never said I had working macros in there.
Im currently partaking in an online vba course however, looking at the modules, I dont think they will cover this particular issue, hense why i turned to a forum.

As for my request, you've done half of what i requested (thank you again), I just need to know how to replicate it as the code above is not working

If cell in column I = "Worker 1 to review" then the cell adjacent in column E needs to change to "not started"
And If the cell in column E is selected as "Ready for worker 2" then the cell adjacent in column I needs to change to "Not started"

Sorry for the late reply.

That worked perfectly. Thank you