Updating lists based on a table.

[HomeTek]

Hi all.

I have a list of items that i have converted into a table (because it makes it easier to update list validation drop downs that I have elsewhere).

However, I have several other lists which use the items in this list as a source, but if I update the table by adding extra items, I then have to also manually update the other lists that I have which use this table as a source.

Is there anyway that this can be automated, perhaps similar to how when I update the table the items in my list validation drop down options also update.

The below is just an example of what I mean.

Thanks

 

[mrshl9898]

Sounds like you need to turn the second list into a table.

Select somewhere in in and press ctrl+T then ok.

 

[HomeTek]

Sounds like you need to turn the second list into a t8able.

Select somewhere in in and press ctrl+T then ok.

Thanks,

That's something I did originally try, but it didn’t solve the problem of needing to update it manually.

At present all the items in that secondary list have the following formula

=ListSource[@RangeSource]

Then of course I still need to drag the formula down for it to pick up further items if they have been added to the main source list.

 

[Akuini]

Hi, HomeTek
Try this:
- Convert your secondary list to a table
- So say your table name are Table1 & Table2 (and I assume both are in the same sheet), put this code in the sheets code module:

[FONT=lucida console][COLOR=Royalblue]Private[/COLOR] [COLOR=Royalblue]Sub[/COLOR] Worksheet_Change([COLOR=Royalblue]ByVal[/COLOR] Target [COLOR=Royalblue]As[/COLOR] Range)
[COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] Intersect(Target, Me.ListObjects([COLOR=brown]"Table1"[/COLOR]).DataBodyRange) [COLOR=Royalblue]Is[/COLOR] [COLOR=Royalblue]Nothing[/COLOR] [COLOR=Royalblue]Then[/COLOR]
    Application.EnableEvents = [COLOR=Royalblue]False[/COLOR]
    n = Me.ListObjects([COLOR=brown]"Table1"[/COLOR]).Range.Rows.Count
    [COLOR=Royalblue]With[/COLOR] Me.ListObjects([COLOR=brown]"Table2"[/COLOR])
    .Resize .Range.Resize(n)
    .DataBodyRange.Value = Me.ListObjects([COLOR=brown]"Table1"[/COLOR]).DataBodyRange.Value
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
    Application.EnableEvents = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]