Lookup table w/ Dynamic Named List
 

I'm trying to combine two solutions I found in this forum onto my WS. I have
two columns Category and SubCategory. I am using a lookup table so that when
the user selects an entry for the Category, the list that is available in the
SubCategory depends on which item is selected in the Category.

Independently, I also have been able to make work the concept of dynamic
list where the items that are displayed in the dropdown list is determined by
a forumula in the Refers To of the Define Name list dialog. What I want to do
is have the SubCategory lists to be dynamic.

The problem I have is that when I change the Refers To text to be the
Dynamic List formula, the list no longer displays when the corresponding
Category is displayed. That Name list also nolonger appears in the Name Box
at the left of menu bars. The list DOES still show up in the Define Name
dialog.

Here are the formulas I'm using:

Category cells validation:
Allow: List
Source: =Category

SubCategory cells Validation:
Allow: List
Source:
=INDIRECT(VLOOKUP(F52,LookupList,2,0))

Of Course, F52 is the Category cell that is used to obtain the value to
lookup in the LookupList. The LookupList is defined correctly covering the
two columns of cells as needed.

The two formulas I'm trying to use for the SubCategory Lists a

='Budget Template'!$W$5:$W$15
=OFFSET('Budget Template'!$W$5,0,0,COUNTA('Budget Template'!$W$5:$W$15),1)

When the List is defined as the first forumula, the lookup list works great.
When defined as a dynamic list per the second, it doesn't work. I've
confirmed that the dynamic list works by itself by setting other cell's
validation to List and Source to =SubCatetory (SubCategory list is located in
W5:W15)

Any insights would be appreciated,

Thank you
John

Lookup table w/ Dynamic Named List
 

Can you upload your sample file using a free filehost,
then post a link to it here?

Easier to see and tinker with

For example, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here in your reply

(Desensitize your sample as appropriate)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---

Lookup table w/ Dynamic Named List
 

There are instructions for using dynamic lists with dependent data
validation here, at the bottom of the page:

http://www.contextures.com/xlDataVal02.html

DocBrown wrote:
I'm trying to combine two solutions I found in this forum onto my WS. I have
two columns Category and SubCategory. I am using a lookup table so that when
the user selects an entry for the Category, the list that is available in the
SubCategory depends on which item is selected in the Category.

Independently, I also have been able to make work the concept of dynamic
list where the items that are displayed in the dropdown list is determined by
a forumula in the Refers To of the Define Name list dialog. What I want to do
is have the SubCategory lists to be dynamic.

The problem I have is that when I change the Refers To text to be the
Dynamic List formula, the list no longer displays when the corresponding
Category is displayed. That Name list also nolonger appears in the Name Box
at the left of menu bars. The list DOES still show up in the Define Name
dialog.

Here are the formulas I'm using:

Category cells validation:
Allow: List
Source: =Category

SubCategory cells Validation:
Allow: List
Source:
=INDIRECT(VLOOKUP(F52,LookupList,2,0))

Of Course, F52 is the Category cell that is used to obtain the value to
lookup in the LookupList. The LookupList is defined correctly covering the
two columns of cells as needed.

The two formulas I'm trying to use for the SubCategory Lists a

='Budget Template'!$W$5:$W$15
=OFFSET('Budget Template'!$W$5,0,0,COUNTA('Budget Template'!$W$5:$W$15),1)

When the List is defined as the first forumula, the lookup list works great.
When defined as a dynamic list per the second, it doesn't work. I've
confirmed that the dynamic list works by itself by setting other cell's
validation to List and Source to =SubCatetory (SubCategory list is located in
W5:W15)

Any insights would be appreciated,

Thank you
John


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Lookup table w/ Dynamic Named List
 

Yes, I've been to this site. It's great. That worked like a charm. I even
modified it some to accomondate how I want to manage the lists.

Thanks!
John

"Debra Dalgleish" wrote:

There are instructions for using dynamic lists with dependent data
validation here, at the bottom of the page:

http://www.contextures.com/xlDataVal02.html

DocBrown wrote:
I'm trying to combine two solutions I found in this forum onto my WS. I have
two columns Category and SubCategory. I am using a lookup table so that when
the user selects an entry for the Category, the list that is available in the
SubCategory depends on which item is selected in the Category.

Independently, I also have been able to make work the concept of dynamic
list where the items that are displayed in the dropdown list is determined by
a forumula in the Refers To of the Define Name list dialog. What I want to do
is have the SubCategory lists to be dynamic.

The problem I have is that when I change the Refers To text to be the
Dynamic List formula, the list no longer displays when the corresponding
Category is displayed. That Name list also nolonger appears in the Name Box
at the left of menu bars. The list DOES still show up in the Define Name
dialog.

Here are the formulas I'm using:

Category cells validation:
Allow: List
Source: =Category

SubCategory cells Validation:
Allow: List
Source:
=INDIRECT(VLOOKUP(F52,LookupList,2,0))

Of Course, F52 is the Category cell that is used to obtain the value to
lookup in the LookupList. The LookupList is defined correctly covering the
two columns of cells as needed.

The two formulas I'm trying to use for the SubCategory Lists a

='Budget Template'!$W$5:$W$15
=OFFSET('Budget Template'!$W$5,0,0,COUNTA('Budget Template'!$W$5:$W$15),1)

When the List is defined as the first forumula, the lookup list works great.
When defined as a dynamic list per the second, it doesn't work. I've
confirmed that the dynamic list works by itself by setting other cell's
validation to List and Source to =SubCatetory (SubCategory list is located in
W5:W15)

Any insights would be appreciated,

Thank you
John


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com