I am trying to limit data entry options into a worksheet based on other
information already 'correctly' entered. Based on a number of criteria I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are BracketList_A,
BracketList_B and BracketList_C where these named ranges containg the
brackets that are appropriate for selection by the user. These named ranges
are Dynamic Ranges allowing us to update the data table as needed.

Based on what I'd read in Debra Dalgleish's site and in the help files I
thought that this would work... I don't get any errors but the drop down
list in the data validation is empty.

Too much info?

Hi!

Using Indirect and dynamic ranges won't work. The reason the drop down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the column headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source formula to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based on other
information already 'correctly' entered. Based on a number of criteria I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges containg the
brackets that are appropriate for selection by the user. These named
ranges
are Dynamic Ranges allowing us to update the data table as needed.

Based on what I'd read in Debra Dalgleish's site and in the help files I
thought that this would work... I don't get any errors but the drop down
list in the data validation is empty.

Too much info?

Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the drop down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the column headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source formula to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based on other
information already 'correctly' entered. Based on a number of criteria I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges containg the
brackets that are appropriate for selection by the user. These named
ranges
are Dynamic Ranges allowing us to update the data table as needed.

Based on what I'd read in Debra Dalgleish's site and in the help files I
thought that this would work... I don't get any errors but the drop down
list in the data validation is empty.

Too much info?

Biff,

When I tried the same formula in a blank cell on my worksheet it evaluated
to a $VALUE error. When I put the listA, listB, listC named ranges in quotes
ie "listA, "listB", "listC" it returned the correct match... but when I tried
this same thing in the data validation formula bar I get an error (of
course, non-traceable).

??

"Steve E" wrote:

Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the drop down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the column headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source formula to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based on other
information already 'correctly' entered. Based on a number of criteria I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges containg the
brackets that are appropriate for selection by the user. These named
ranges
are Dynamic Ranges allowing us to update the data table as needed.

Based on what I'd read in Debra Dalgleish's site and in the help files I
thought that this would work... I don't get any errors but the drop down
list in the data validation is empty.

Too much info?

wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box?

You can! I often write the formula on the worksheet then copy/paste it into
the box. Those little boxes are a real PITA!

CTRL C to copy
CTRL V to paste

Thanks for the help. Unfortunately, I seem to only get listB to populate
the drop down... even though cell AC18 evaluates to listA...

Did you make a list of the range names? Or, did you already have them listed
as column headers?

What are the EXACT cells adresses of those names?

Post the EXACT formula you tried.

If all else fails you can send the file to me and I'll set it up for you.
Or, I can put together a sample file that demonstrates this.

Biff

"Steve E" wrote in message
...
Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the drop down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the column headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source formula to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based on other
information already 'correctly' entered. Based on a number of criteria
I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges containg the
brackets that are appropriate for selection by the user. These named
ranges
are Dynamic Ranges allowing us to update the data table as needed.

Based on what I'd read in Debra Dalgleish's site and in the help files
I
thought that this would work... I don't get any errors but the drop
down
list in the data validation is empty.

Too much info?

The formula won't work in a worksheet cell.

DON'T enclose the range names in quotes. They'll be evaluated as TEXT
strings.

See my other reply!

Biff

"Steve E" wrote in message
...
Biff,

When I tried the same formula in a blank cell on my worksheet it evaluated
to a $VALUE error. When I put the listA, listB, listC named ranges in
quotes
ie "listA, "listB", "listC" it returned the correct match... but when I
tried
this same thing in the data validation formula bar I get an error (of
course, non-traceable).

??

"Steve E" wrote:

Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the formula
bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the drop down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the column
headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source formula
to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based on
other
information already 'correctly' entered. Based on a number of
criteria I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges containg the
brackets that are appropriate for selection by the user. These named
ranges
are Dynamic Ranges allowing us to update the data table as needed.

Based on what I'd read in Debra Dalgleish's site and in the help
files I
thought that this would work... I don't get any errors but the drop
down
list in the data validation is empty.

Too much info?

Hi Biff,

Finally able to get back to solving this problem.

The exact formula that I entered in the data validation formula bar is:
=CHOOSE(MATCH(AC18,$AC$11:$AC$13,0),BracketList_A, BracketList_B,BracketList_C)

My list of range names is AC11=BracketList_A, AC12=BracketListB,
AC13=BracketLIst_C

The named ranges are on a seperate worksheet in the same workbook

Regardless of what AC18 (see orig opst for that formula) evaluates to I only
get BracketList_B range in the drop down list...

I can send you the file if it will be easier...

THanks,

Steve



"Biff" wrote:

wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box?

You can! I often write the formula on the worksheet then copy/paste it into
the box. Those little boxes are a real PITA!

CTRL C to copy
CTRL V to paste

Thanks for the help. Unfortunately, I seem to only get listB to populate
the drop down... even though cell AC18 evaluates to listA...

Did you make a list of the range names? Or, did you already have them listed
as column headers?

What are the EXACT cells adresses of those names?

Post the EXACT formula you tried.

If all else fails you can send the file to me and I'll set it up for you.
Or, I can put together a sample file that demonstrates this.

Biff

"Steve E" wrote in message
...
Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the drop down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the column headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source formula to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based on other
information already 'correctly' entered. Based on a number of criteria
I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges containg the
brackets that are appropriate for selection by the user. These named
ranges
are Dynamic Ranges allowing us to update the data table as needed.

Based on what I'd read in Debra Dalgleish's site and in the help files
I
thought that this would work... I don't get any errors but the drop
down
list in the data validation is empty.

Too much info?

I can send you the file if it will be easier...

Ok, I'm at:

xl can help at comcast period net

Remove "can" and and change the obvious.

Biff

"Steve E" wrote in message
...
Hi Biff,

Finally able to get back to solving this problem.

The exact formula that I entered in the data validation formula bar is:
=CHOOSE(MATCH(AC18,$AC$11:$AC$13,0),BracketList_A, BracketList_B,BracketList_C)

My list of range names is AC11=BracketList_A, AC12=BracketListB,
AC13=BracketLIst_C

The named ranges are on a seperate worksheet in the same workbook

Regardless of what AC18 (see orig opst for that formula) evaluates to I
only
get BracketList_B range in the drop down list...

I can send you the file if it will be easier...

THanks,

Steve



"Biff" wrote:

wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box?

You can! I often write the formula on the worksheet then copy/paste it
into
the box. Those little boxes are a real PITA!

CTRL C to copy
CTRL V to paste

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Did you make a list of the range names? Or, did you already have them
listed
as column headers?

What are the EXACT cells adresses of those names?

Post the EXACT formula you tried.

If all else fails you can send the file to me and I'll set it up for you.
Or, I can put together a sample file that demonstrates this.

Biff

"Steve E" wrote in message
...
Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the formula
bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the drop down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the column
headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source formula
to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based on
other
information already 'correctly' entered. Based on a number of
criteria
I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges containg
the
brackets that are appropriate for selection by the user. These
named
ranges
are Dynamic Ranges allowing us to update the data table as needed.

Based on what I'd read in Debra Dalgleish's site and in the help
files
I
thought that this would work... I don't get any errors but the drop
down
list in the data validation is empty.

Too much info?

Biff,

Thanks so much for your help. The file that you "tweaked" works fine... but
now I am having the same problem with a variant of this...

Any ideas as to what I'm doing wrong?

"Biff" wrote:

I can send you the file if it will be easier...

Ok, I'm at:

xl can help at comcast period net

Remove "can" and and change the obvious.

Biff

"Steve E" wrote in message
...
Hi Biff,

Finally able to get back to solving this problem.

The exact formula that I entered in the data validation formula bar is:
=CHOOSE(MATCH(AC18,$AC$11:$AC$13,0),BracketList_A, BracketList_B,BracketList_C)

My list of range names is AC11=BracketList_A, AC12=BracketListB,
AC13=BracketLIst_C

The named ranges are on a seperate worksheet in the same workbook

Regardless of what AC18 (see orig opst for that formula) evaluates to I
only
get BracketList_B range in the drop down list...

I can send you the file if it will be easier...

THanks,

Steve



"Biff" wrote:

wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box?

You can! I often write the formula on the worksheet then copy/paste it
into
the box. Those little boxes are a real PITA!

CTRL C to copy
CTRL V to paste

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Did you make a list of the range names? Or, did you already have them
listed
as column headers?

What are the EXACT cells adresses of those names?

Post the EXACT formula you tried.

If all else fails you can send the file to me and I'll set it up for you.
Or, I can put together a sample file that demonstrates this.

Biff

"Steve E" wrote in message
...
Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the formula
bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the drop down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the column
headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source formula
to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based on
other
information already 'correctly' entered. Based on a number of
criteria
I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges containg
the
brackets that are appropriate for selection by the user. These
named
ranges
are Dynamic Ranges allowing us to update the data table as needed.

Based on what I'd read in Debra Dalgleish's site and in the help
files
I
thought that this would work... I don't get any errors but the drop
down
list in the data validation is empty.

Too much info?

Same file but different lists?

Need the details. I still have a copy of your file so I'll be able to see
what you're having problems with.

Biff

"Steve E" wrote in message
...
Biff,

Thanks so much for your help. The file that you "tweaked" works fine...
but
now I am having the same problem with a variant of this...

Any ideas as to what I'm doing wrong?

"Biff" wrote:

I can send you the file if it will be easier...

Ok, I'm at:

xl can help at comcast period net

Remove "can" and and change the obvious.

Biff

"Steve E" wrote in message
...
Hi Biff,

Finally able to get back to solving this problem.

The exact formula that I entered in the data validation formula bar is:
=CHOOSE(MATCH(AC18,$AC$11:$AC$13,0),BracketList_A, BracketList_B,BracketList_C)

My list of range names is AC11=BracketList_A, AC12=BracketListB,
AC13=BracketLIst_C

The named ranges are on a seperate worksheet in the same workbook

Regardless of what AC18 (see orig opst for that formula) evaluates to I
only
get BracketList_B range in the drop down list...

I can send you the file if it will be easier...

THanks,

Steve



"Biff" wrote:

wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box?

You can! I often write the formula on the worksheet then copy/paste it
into
the box. Those little boxes are a real PITA!

CTRL C to copy
CTRL V to paste

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Did you make a list of the range names? Or, did you already have them
listed
as column headers?

What are the EXACT cells adresses of those names?

Post the EXACT formula you tried.

If all else fails you can send the file to me and I'll set it up for
you.
Or, I can put together a sample file that demonstrates this.

Biff

"Steve E" wrote in message
...
Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the
formula
bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the drop
down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the column
headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source
formula
to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based on
other
information already 'correctly' entered. Based on a number of
criteria
I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges containg
the
brackets that are appropriate for selection by the user. These
named
ranges
are Dynamic Ranges allowing us to update the data table as
needed.

Based on what I'd read in Debra Dalgleish's site and in the help
files
I
thought that this would work... I don't get any errors but the
drop
down
list in the data validation is empty.

Too much info?

Hi Biff,

Same file. I changed the named range sequence and it worked... do the named
ranges need to be in a certain sequence locationally on the referenced
spreadsheet?

Ex: named range "A" was in cell 'Input LIst'!$U$2 named "NA" and named
range "B" wan in cell 'Input LIst'!$V$2 and is named "HembarFinish" and the
drop down list was stuck on the "HembarFinish" list. When I changed the cell
addresses so that the names were in alpha order L2R it worked...

Do the MATCH and CHOOSE functions require the lists to be sorted?

So... for now... all is good...

"Biff" wrote:

Same file but different lists?

Need the details. I still have a copy of your file so I'll be able to see
what you're having problems with.

Biff

"Steve E" wrote in message
...
Biff,

Thanks so much for your help. The file that you "tweaked" works fine...
but
now I am having the same problem with a variant of this...

Any ideas as to what I'm doing wrong?

"Biff" wrote:

I can send you the file if it will be easier...

Ok, I'm at:

xl can help at comcast period net

Remove "can" and and change the obvious.

Biff

"Steve E" wrote in message
...
Hi Biff,

Finally able to get back to solving this problem.

The exact formula that I entered in the data validation formula bar is:
=CHOOSE(MATCH(AC18,$AC$11:$AC$13,0),BracketList_A, BracketList_B,BracketList_C)

My list of range names is AC11=BracketList_A, AC12=BracketListB,
AC13=BracketLIst_C

The named ranges are on a seperate worksheet in the same workbook

Regardless of what AC18 (see orig opst for that formula) evaluates to I
only
get BracketList_B range in the drop down list...

I can send you the file if it will be easier...

THanks,

Steve



"Biff" wrote:

wouldn't it be nice if you could cut and paste into the formula bar
in the data validation box?

You can! I often write the formula on the worksheet then copy/paste it
into
the box. Those little boxes are a real PITA!

CTRL C to copy
CTRL V to paste

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Did you make a list of the range names? Or, did you already have them
listed
as column headers?

What are the EXACT cells adresses of those names?

Post the EXACT formula you tried.

If all else fails you can send the file to me and I'll set it up for
you.
Or, I can put together a sample file that demonstrates this.

Biff

"Steve E" wrote in message
...
Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the
formula
bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the drop
down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF! error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the column
headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source
formula
to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based on
other
information already 'correctly' entered. Based on a number of
criteria
I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges containg
the
brackets that are appropriate for selection by the user. These
named
ranges
are Dynamic Ranges allowing us to update the data table as
needed.

Based on what I'd read in Debra Dalgleish's site and in the help
files
I
thought that this would work... I don't get any errors but the
drop
down
list in the data validation is empty.

Too much info?

Do the MATCH and CHOOSE functions require the lists to be sorted?

Not sorted in the sense that they be in alpha order.

Let's take the bracket lists as an example.

On sheet Input Lists, the named lists are in a certain order:

N2 = BracketList_A
O2 = BracketList_B
P2 = BracketList_C

The way the validation formula works:

=CHOOSE(MATCH(AC18,BracketLists,0),BracketList_A,B racketList_B,BracketList_C)

The Match function returns the number of the lookup_values RELATIVE position
within the lookup_array. The lookup_array is the named range BracketLists
that refers to Input Lists!N2:P2.

Let's say that AC18 (the lookup_value) = BracketList_B. The Match function
will return 2 because BracketList_B is in the 2nd position of the
lookup_array. If AC18 = BL_A then Match would return 1 because BL_A is in
the 1st position. BL_C is in the 3rd position. The result of the Match
function is then passed to the Choose function.

The Choose function also works based on position. Let's see how that looks
AFTER the Match function has passed its value:

=CHOOSE(2,BracketList_A,BracketList_B,BracketList_ C)

The 2 is the index_number argument. That means to return the 2nd
value_argument which is the dynamic named range BracketList_B. If the
index_number was 3 then BL_C would be returned. If the index_number was 1
then BL_A would be returned.

So, to put it all in a logical sense:

The order of the items in the Match lookup_array MUST be in the same order
of the Choose value_argument (or vice versa) otherwise you'll get the
incorrect list appearing in the drop down.

Biff

"Steve E" wrote in message
...
Hi Biff,

Same file. I changed the named range sequence and it worked... do the
named
ranges need to be in a certain sequence locationally on the referenced
spreadsheet?

Ex: named range "A" was in cell 'Input LIst'!$U$2 named "NA" and named
range "B" wan in cell 'Input LIst'!$V$2 and is named "HembarFinish" and
the
drop down list was stuck on the "HembarFinish" list. When I changed the
cell
addresses so that the names were in alpha order L2R it worked...

Do the MATCH and CHOOSE functions require the lists to be sorted?

So... for now... all is good...

"Biff" wrote:

Same file but different lists?

Need the details. I still have a copy of your file so I'll be able to see
what you're having problems with.

Biff

"Steve E" wrote in message
...
Biff,

Thanks so much for your help. The file that you "tweaked" works
fine...
but
now I am having the same problem with a variant of this...

Any ideas as to what I'm doing wrong?

"Biff" wrote:

I can send you the file if it will be easier...

Ok, I'm at:

xl can help at comcast period net

Remove "can" and and change the obvious.

Biff

"Steve E" wrote in message
...
Hi Biff,

Finally able to get back to solving this problem.

The exact formula that I entered in the data validation formula bar
is:
=CHOOSE(MATCH(AC18,$AC$11:$AC$13,0),BracketList_A, BracketList_B,BracketList_C)

My list of range names is AC11=BracketList_A, AC12=BracketListB,
AC13=BracketLIst_C

The named ranges are on a seperate worksheet in the same workbook

Regardless of what AC18 (see orig opst for that formula) evaluates
to I
only
get BracketList_B range in the drop down list...

I can send you the file if it will be easier...

THanks,

Steve



"Biff" wrote:

wouldn't it be nice if you could cut and paste into the formula
bar
in the data validation box?

You can! I often write the formula on the worksheet then copy/paste
it
into
the box. Those little boxes are a real PITA!

CTRL C to copy
CTRL V to paste

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Did you make a list of the range names? Or, did you already have
them
listed
as column headers?

What are the EXACT cells adresses of those names?

Post the EXACT formula you tried.

If all else fails you can send the file to me and I'll set it up
for
you.
Or, I can put together a sample file that demonstrates this.

Biff

"Steve E" wrote in message
...
Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the
formula
bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the
drop
down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF!
error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the
column
headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source
formula
to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based
on
other
information already 'correctly' entered. Based on a number of
criteria
I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges
containg
the
brackets that are appropriate for selection by the user.
These
named
ranges
are Dynamic Ranges allowing us to update the data table as
needed.

Based on what I'd read in Debra Dalgleish's site and in the
help
files
I
thought that this would work... I don't get any errors but
the
drop
down
list in the data validation is empty.

Too much info?

BINGO.

Now I think I get it.

Thanks!

"Biff" wrote:

Do the MATCH and CHOOSE functions require the lists to be sorted?

Not sorted in the sense that they be in alpha order.

Let's take the bracket lists as an example.

On sheet Input Lists, the named lists are in a certain order:

N2 = BracketList_A
O2 = BracketList_B
P2 = BracketList_C

The way the validation formula works:

=CHOOSE(MATCH(AC18,BracketLists,0),BracketList_A,B racketList_B,BracketList_C)

The Match function returns the number of the lookup_values RELATIVE position
within the lookup_array. The lookup_array is the named range BracketLists
that refers to Input Lists!N2:P2.

Let's say that AC18 (the lookup_value) = BracketList_B. The Match function
will return 2 because BracketList_B is in the 2nd position of the
lookup_array. If AC18 = BL_A then Match would return 1 because BL_A is in
the 1st position. BL_C is in the 3rd position. The result of the Match
function is then passed to the Choose function.

The Choose function also works based on position. Let's see how that looks
AFTER the Match function has passed its value:

=CHOOSE(2,BracketList_A,BracketList_B,BracketList_ C)

The 2 is the index_number argument. That means to return the 2nd
value_argument which is the dynamic named range BracketList_B. If the
index_number was 3 then BL_C would be returned. If the index_number was 1
then BL_A would be returned.

So, to put it all in a logical sense:

The order of the items in the Match lookup_array MUST be in the same order
of the Choose value_argument (or vice versa) otherwise you'll get the
incorrect list appearing in the drop down.

Biff

"Steve E" wrote in message
...
Hi Biff,

Same file. I changed the named range sequence and it worked... do the
named
ranges need to be in a certain sequence locationally on the referenced
spreadsheet?

Ex: named range "A" was in cell 'Input LIst'!$U$2 named "NA" and named
range "B" wan in cell 'Input LIst'!$V$2 and is named "HembarFinish" and
the
drop down list was stuck on the "HembarFinish" list. When I changed the
cell
addresses so that the names were in alpha order L2R it worked...

Do the MATCH and CHOOSE functions require the lists to be sorted?

So... for now... all is good...

"Biff" wrote:

Same file but different lists?

Need the details. I still have a copy of your file so I'll be able to see
what you're having problems with.

Biff

"Steve E" wrote in message
...
Biff,

Thanks so much for your help. The file that you "tweaked" works
fine...
but
now I am having the same problem with a variant of this...

Any ideas as to what I'm doing wrong?

"Biff" wrote:

I can send you the file if it will be easier...

Ok, I'm at:

xl can help at comcast period net

Remove "can" and and change the obvious.

Biff

"Steve E" wrote in message
...
Hi Biff,

Finally able to get back to solving this problem.

The exact formula that I entered in the data validation formula bar
is:
=CHOOSE(MATCH(AC18,$AC$11:$AC$13,0),BracketList_A, BracketList_B,BracketList_C)

My list of range names is AC11=BracketList_A, AC12=BracketListB,
AC13=BracketLIst_C

The named ranges are on a seperate worksheet in the same workbook

Regardless of what AC18 (see orig opst for that formula) evaluates
to I
only
get BracketList_B range in the drop down list...

I can send you the file if it will be easier...

THanks,

Steve



"Biff" wrote:

wouldn't it be nice if you could cut and paste into the formula
bar
in the data validation box?

You can! I often write the formula on the worksheet then copy/paste
it
into
the box. Those little boxes are a real PITA!

CTRL C to copy
CTRL V to paste

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Did you make a list of the range names? Or, did you already have
them
listed
as column headers?

What are the EXACT cells adresses of those names?

Post the EXACT formula you tried.

If all else fails you can send the file to me and I'll set it up
for
you.
Or, I can put together a sample file that demonstrates this.

Biff

"Steve E" wrote in message
...
Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB to
populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the
formula
bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the
drop
down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF!
error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the
column
headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the source
formula
to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet based
on
other
information already 'correctly' entered. Based on a number of
criteria
I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40 are
BracketList_A,
BracketList_B and BracketList_C where these named ranges
containg
the
brackets that are appropriate for selection by the user.
These
named
ranges
are Dynamic Ranges allowing us to update the data table as
needed.

Based on what I'd read in Debra Dalgleish's site and in the
help
files
I
thought that this would work... I don't get any errors but
the
drop
down
list in the data validation is empty.

Too much info?

Good deal!

You'll find that once you reach that "Bingo" moment most of this stuff
becomes rather easy.

Biff

"Steve E" wrote in message
...
BINGO.

Now I think I get it.

Thanks!

"Biff" wrote:

Do the MATCH and CHOOSE functions require the lists to be sorted?

Not sorted in the sense that they be in alpha order.

Let's take the bracket lists as an example.

On sheet Input Lists, the named lists are in a certain order:

N2 = BracketList_A
O2 = BracketList_B
P2 = BracketList_C

The way the validation formula works:

=CHOOSE(MATCH(AC18,BracketLists,0),BracketList_A,B racketList_B,BracketList_C)

The Match function returns the number of the lookup_values RELATIVE
position
within the lookup_array. The lookup_array is the named range BracketLists
that refers to Input Lists!N2:P2.

Let's say that AC18 (the lookup_value) = BracketList_B. The Match
function
will return 2 because BracketList_B is in the 2nd position of the
lookup_array. If AC18 = BL_A then Match would return 1 because BL_A is in
the 1st position. BL_C is in the 3rd position. The result of the Match
function is then passed to the Choose function.

The Choose function also works based on position. Let's see how that
looks
AFTER the Match function has passed its value:

=CHOOSE(2,BracketList_A,BracketList_B,BracketList_ C)

The 2 is the index_number argument. That means to return the 2nd
value_argument which is the dynamic named range BracketList_B. If the
index_number was 3 then BL_C would be returned. If the index_number was 1
then BL_A would be returned.

So, to put it all in a logical sense:

The order of the items in the Match lookup_array MUST be in the same
order
of the Choose value_argument (or vice versa) otherwise you'll get the
incorrect list appearing in the drop down.

Biff

"Steve E" wrote in message
...
Hi Biff,

Same file. I changed the named range sequence and it worked... do the
named
ranges need to be in a certain sequence locationally on the referenced
spreadsheet?

Ex: named range "A" was in cell 'Input LIst'!$U$2 named "NA" and named
range "B" wan in cell 'Input LIst'!$V$2 and is named "HembarFinish" and
the
drop down list was stuck on the "HembarFinish" list. When I changed
the
cell
addresses so that the names were in alpha order L2R it worked...

Do the MATCH and CHOOSE functions require the lists to be sorted?

So... for now... all is good...

"Biff" wrote:

Same file but different lists?

Need the details. I still have a copy of your file so I'll be able to
see
what you're having problems with.

Biff

"Steve E" wrote in message
...
Biff,

Thanks so much for your help. The file that you "tweaked" works
fine...
but
now I am having the same problem with a variant of this...

Any ideas as to what I'm doing wrong?

"Biff" wrote:

I can send you the file if it will be easier...

Ok, I'm at:

xl can help at comcast period net

Remove "can" and and change the obvious.

Biff

"Steve E" wrote in message
...
Hi Biff,

Finally able to get back to solving this problem.

The exact formula that I entered in the data validation formula
bar
is:
=CHOOSE(MATCH(AC18,$AC$11:$AC$13,0),BracketList_A, BracketList_B,BracketList_C)

My list of range names is AC11=BracketList_A, AC12=BracketListB,
AC13=BracketLIst_C

The named ranges are on a seperate worksheet in the same workbook

Regardless of what AC18 (see orig opst for that formula)
evaluates
to I
only
get BracketList_B range in the drop down list...

I can send you the file if it will be easier...

THanks,

Steve



"Biff" wrote:

wouldn't it be nice if you could cut and paste into the formula
bar
in the data validation box?

You can! I often write the formula on the worksheet then
copy/paste
it
into
the box. Those little boxes are a real PITA!

CTRL C to copy
CTRL V to paste

Thanks for the help. Unfortunately, I seem to only get listB
to
populate
the drop down... even though cell AC18 evaluates to listA...

Did you make a list of the range names? Or, did you already have
them
listed
as column headers?

What are the EXACT cells adresses of those names?

Post the EXACT formula you tried.

If all else fails you can send the file to me and I'll set it up
for
you.
Or, I can put together a sample file that demonstrates this.

Biff

"Steve E" wrote in message
...
Hi BIff,

Thanks for the help. Unfortunately, I seem to only get listB
to
populate
the drop down... even though cell AC18 evaluates to listA...

Any ideas?

And... wouldn't it be nice if you could cut and paste into the
formula
bar
in the data validation box?

Appreciate your help.

Steve

"Biff" wrote:

Hi!

Using Indirect and dynamic ranges won't work. The reason the
drop
down
doesn't work is because =INDIRECT(AC18) evaluates to a #REF!
error.

Try this as the drop down source:

List the named ranges somewhere,say, G1:I1.

G1 = listA
H1 = listB
I1 = listC

Or, you may already have the names listed somewhere as the
column
headers
for the named ranges. If so just refer to those cells:

=CHOOSE(MATCH(AC18,G1:I1,0),listA,listB,listC)

You can have up to 29 named ranges and use CHOOSE.

When your formula returns "Get Quote" that'll cause the
source
formula
to
error and the drop down won't work.

Biff

"Steve E" wrote in message
...
I am trying to limit data entry options into a worksheet
based
on
other
information already 'correctly' entered. Based on a number
of
criteria
I
return a Named Range as a result of the formula:

I am using =INDIRECT(AC18) as the data validation formula

Cell AC18:
=IF(ISNA(IF(TubeRequired=VLOOKUP(SystemSelected,'I nput
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get
Quote"))),"",(IF(TubeRequired=VLOOKUP(SystemSelect ed,'Input
Lists'!$A$3:$E$40,2,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,3,FALSE),IF(TubeRequired=VLOOKUP (SystemSelected,'Input
Lists'!$A$3:$E$40,4,FALSE),VLOOKUP(SystemSelected, 'Input
Lists'!$A$3:$E$40,5,FALSE),"Get Quote"))))

The resulting named ranges in the 'InputLists'!$A$3:$E$40
are
BracketList_A,
BracketList_B and BracketList_C where these named ranges
containg
the
brackets that are appropriate for selection by the user.
These
named
ranges
are Dynamic Ranges allowing us to update the data table as
needed.

Based on what I'd read in Debra Dalgleish's site and in the
help
files
I
thought that this would work... I don't get any errors but
the
drop
down
list in the data validation is empty.

Too much info?