I am trying to set up a data validation list from several Named
Ranges.

The named ranges are on a sheet called 'LookupLists'
1st Named Range is called 'PurchasedFrom', which lists Shop1, Shop2,
Shop3 (cells K4:K15)
2nd Named Range lists items available at Shop1 (currently cells
M4:M10)
3rd Named Range lists items available at Shop2 (currently cells
P4:P10)
and so on.

When selecting a Sheet called 'PurchasedEntry', cell b5, the
validation lists Shop1, Shop2, Shop3 (which works great).
However, when say Shop1 is chosen, i want the validation list in
'PurchasedEntry', cell c5 to display items available from Shop1 only.

I have done this, however, the problem is that the shop contents lists
can grow, and i want the validation list range to grow automatically,
so i have tried the following validation formula picked up from
various web sources:-

(which should list the contents of Shop1)

=OFFSET(LookupLists!$M$4,0,0,COUNTA(LookupLists!$M :$M)-2,1)

Unfortunately, i can't get it to work.
Cell c5 shows the pulldown tab, but when clicked, it does not
function.

Can anybody please help?