I have a table of data, 3 columns. Number, Name, Location.

I would like to make another table with the Locations across the horizontal, and then the appropriate names listed under that location on the vertical. The tables are on separate sheets within the same workbook.

My Friday afternoon, sleep depraved brain is struggling.

Not too clear what you want…

If it’s all in one table listed vertically and you want it horizontally…

Paste Spectial > Transpose ?

Sounds like a pivot-table job.

i don’t want to transpose the list.

The first table lists a number of towns in the name column, and the Tv region within which these towns sit in the location column.

I would like the 2nd table to list the Tv regions, and then pick up which towns on the list fall under that region. The first table is updated regularly so I need the 2nd table to update automatically.

Pivot table innit

pivot table it ain’t unfortunately 🙁

EDIT – not quite true. You can do it with a pivot table but you won’t get a list like the OP wants with the Towns across the top and the TV regions in the rows underneath. What you can get is a a list of towns in rows with an ‘x’ to expand the list and then show the TV regions.

Add a column of line numbers to both tables, in your second table use a vlookup against region and row number, then filler out all the #N/As.