Yes, it's possible! I can see two options:
A "basic" option with formulae - Sheet 2 could contain a lot of "if" formulae. But this could be a lot of formulae if there is a lot of data in Sheet 1, you would need to remember to copy those formulae down, and there would be gaps on Sheet 2 for rows where Sheet 1 still showed "open".
Probably a better option with macros - you could get the macros to copy across the data when the row on Sheet 1 is closed, putting the data on the next available line. This could either be done based on your existing drop down, or you could have an "open/close" button at the top of the sheet which would change the status in the final column of whatever row was selected when the button was clicked, and copy the data across. The button has the advantage that the macro only runs when the button is clicked. Otherwise if its triggered by the drop down, the macro would need to be running in the background all the time, checking for a change in the status column, which could have a minor impact on speed if its a large/complex spreadsheet.
If you take one of the macro options, what would you want to happen if the row was subsequently re-opened or re-closed? Would you want re-opening to remove the line from Sheet 2 - if so, is there a column containing a unique reference that could be used to find the entry on Sheet 2? Re-closing would create a line on Sheet 2, so if the line wasn't removed on re-opening, you could have a duplicate - would this matter?
I'd be happy to draft a macro if you could provide details of:
- the two sheet names
- the columns containing data, including which is the "closed"/"open" status column
- what you want to happen in the event of re-opening (if it's delete the original row from Sheet 2, which column contains a unique reference)
- whether you want the macro to be based on the existing drop down or a button