I am definitely not an Excel expert, but I'm trying to set up a spreadsheet that allows for multiple departments enter their data. The first department (IGM) enters data that is then populated onto each of the remaining department's sheets. The IGM sheet includes a column that is restricted to choices from a drop down list. I set up the drop down list by creating a table on a separate sheet which will eventually be hidden - Lookups - and then set up a Data Validation to pull the options.
Then to auto-populate the rows onto the other department sheets, I used the following VBA code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("IGMUpdate"), Target) Is Nothing Then
'Sheet5 has purposely been placed first as this will
'be the active sheet we will work from
Sheets(Array("IGM", "EBS", "IP", "Litigation Support", "Accounting")).Select
Else
Me.Select
End If
End Sub
The script functioned exactly as I was hoping and everything I input on the IGM sheet is populated onto the other department sheets - EXCEPT, the selection from the drop down list. That cell is left blank on the other department sheets.
Can anyone explain what I'm doing wrong? Is there some other way to accomplish what I'm trying to do? I can always remove the drop down list and just have the cell be free-form, but it's a cell that is used as a trigger by other departments so I really didn't want to leave any room for error/creativity.
I've included my spreadsheet as an attachment - any help is GREATLY appreciated!
Bookmarks