On 26 July, 18:49, "T. Valko" wrote:
=CHOOSE(MATCH(X1,A1:C1,0),Shop1,Shop2,Shop3)

Ok, you'll have to give the range A1:C1 a defined name since it's located on
a different sheet.

See how I did it in this small sample file:

xMik.xls *14kb

http://cjoint.com/?hAtVaD7Pgw

--
Biff
Microsoft Excel MVP

"Mik" wrote in message

...



On 26 July, 17:11, "T. Valko" wrote:
If you used a formula like this as the source for the dependent drop
downs:

=INDIRECT(cell_ref)

That's your problem.

INDIRECT won't work when the cell_ref refers to a dynamic range.

It's kind of hard to visualize your setup but you can easily work-around
this depending on how many named ranges you have.

If you use column headers that are the same names as the named ranges:

.............A.................B.................. C...
1.........Shop1..........Shop2..........Shop3
2.........item1............item1............item1
3.............................item2............ite m2
4................................................. item3

ShopN are all dynamic ranges.

Then, let's assume X1 is a drop down with these selections: Shop1, Shop2,
Shop3

As the source for the dependent drop down use:

=CHOOSE(MATCH(X1,A1:C1,0),Shop1,Shop2,Shop3)

--
Biff
Microsoft Excel MVP

"Mik" wrote in message

....

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?- Hide quoted text -

- Show quoted text -

Thanks for your reply.

I tried this as a seperate exercise in a new workbook, and it worked
great.

However, when adding to the workbook in question, the validation would
not work, as i currently have all my lookup lists (18 in all and
growing) on a seperate worksheet, and the validation function
apparently does not except reference to other worksheets.

I guess i could add the lookup lists to the active sheet, and hide the
columns or something??

Or am i missing a trick?

Thanks again.- Hide quoted text -

- Show quoted text -



Hello,

Thanks for your response.
Your attachment looks great, and is a big help.

There are further implications....

In the adjacent column to each Item (eg. Item1, Item2 etc..) there is
a unit price.
Is it possible that when the Validation list selects say... shop3,
item2, the cost of item2 is displayed in the adjacent cell to the
validation pulldown?