I was trying to come up with an idea that would make filtered lookups so that only a select number of items in a lookup would appear. For example if you had non-active data in a lookup and only wanted to be able to select from the active ones, but you obviously couldn’t delete the data from that lookup as it would appear in old linked items.
The added problem is that the users were adding the data from excel rather than uploading the new form (I had already written some suitable code for this).
Anyway, with this in mind I thought the following might come in useful for someone else. It is an excel spreadsheet that has the lookups built in and those lookups are linked to the site although the main list is not linked to the site (because then you couldn’t choose where the lookups are selected from) and the data needs to be pasted in in datasheet view.
The lookups themselves would need to be linked exports from the look-up lists. You will firstly need to set up a filtered view of the data you wish to see in your lookup. Export this particular view to your spreadsheet and then use it as your lookup for that row of data (use data validation). When the spreadsheet opens the data would need to be synchronised with the list. This could be done by writing a macro that went through and synchronised them all. It may be necessary to turn macros on for the person who is running them. You can set up the spreadsheet to run the macros on opening.
Then you have a list that looks up to only the data you want it to.
Code for automatically running the macro on loading the sheet:
Private Sub Workbook_Open()
Run ("Synch_Data")
End Sub
Code for synchronising the lists:
Sub Synch_Data()
Sheets("Data").Select
Range("BA2").Select
ActiveSheet.ListObjects("List1").UpdateChanges xlListConflictDialog
Sheets("GapTypes").Select
Range("B2").Select
ActiveSheet.ListObjects("List1").UpdateChanges xlListConflictDialog
Sheets("Data").Select
Range("A1").Select
End Sub
