Validation list from Named Ranges

  • Thread starter Thread starter Mik
  • Start date Start date
M

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
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?
 
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)
 
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 -
Click to expand...

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.
 
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 -
Click to expand...



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?
 
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 -
Click to expand...



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