I have a parent list called called Items List residing on parent site containing columns as Site and Workspace. I created a Site Lookup column and named it as 'Workspace' which references Workspace column from the Items List. Then I am using this 'Workspace' site lookup column in Documents library on subsites under parent.
I have about 5 subsites under root. The Site column in the Items list contains these subsite names like Site 1, Site 2, Site 3, Site 4, Site 5 and relevant workspace names are stored in Workspace column. Below is the screenshot of the list.
Now the challenge is let's say if I am working on Documents Library (It has Worksapce added from existing site columns) under subsite Site 1 then in the Workspace drop-down, I would only want to see Workspace names that are relevant to Site 1 which are Workspace 1, Workspace 2 and Workspace 4 and others should be hidden. Similarly If user is working on Site 2's documents library then he/she should only see Workspace 6 in the Workspace drop-down. So this should be dynamic in nature, like if anything changes in the Items list, the drop-down should account for that.