Validation list from Named Ranges

[Mik]

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
P410)
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?

 

[T. Valko]

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............item2
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)

 

[Mik]

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............item2
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











- 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.

 

[T. Valko]

=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

 

[Mik]

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

See how I did it in this small sample file:

xMik.xls  14kb

http://cjoint.com/?hAtVaD7Pgw

--
Biff
Microsoft Excel MVP












- 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?

 

[T. Valko]

There are further implications....

Yeah, there always is!

Well, now it'll take a complete redo.

Here's the reworked sample file:

xMik(2).xls 16kb

http://cjoint.com/?hExvVhT2hi

The formulas are more complicated.

This would be *much* easier if you setup your List/Price sheet in a vertical
flat database file format. Like this:

Shop1...Item1...10
.............................
Shop2...Item1...15
Shop2...Item2...12
.............................
Shop3...Item1...22
Shop3...Item2...17
Shop3...Item3...30


--
Biff
Microsoft Excel MVP

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












- 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?