Return string of values from range

  • Thread starter Thread starter Robert H
  • Start date Start date
R

Robert H

Is it possible to return string of values from range instead of the
range in a formula. In the following formula I need to reference the
names "FruitList and VegetableList" (which are list headings)

CHOOSE(MATCH(A2&"List",ListRng),FruitList,VegetableList)

this works but I need the headings to be dynamic in the formula

I defined a name "Cat1List" to the location of those list headings.
and tried
CHOOSE(MATCH(A2&"List",ListRng),Cat1List)

CHOOSE just picks up the the range of the name instead of the two
values.
Is ther a function or method to return a string of the two values
(FruitList,VegetableList) from the named range within the formula?
 
Is it possible to return string of values from
range instead of the range in a formula.
Click to expand...

Yes, but you'd have to pass the result of CHOOSE to another function that
can do something with those values.

What are you trying to do? Are you trying to setup dependent drop down
lists?
 
Yes, I am building on the help you previously provided in another
thread. It seems I seldom come op with a simple task :)

It is a series of drop down lists I'm using the Fruit and Veg examples
to work out the details before I incorporate my data.
On an Entry sheet Data Validation is used to establish category and
subcategory choices. The subcategories are dependent and cascade on
each other.

On a worksheet "Cat1Lists"; Cat1List, FruitList and VegetableList
reside as column headings for named ranges.
Cat1List includes Fruit and Vegetables. fruitlist and vegetablelist
have fruits and vegetables...
On sheet "FruitLists" there are lists such as AppleList and PearList
containing varieties of the respective fruit.
there will be a similar sheet "vegetableLists"

I need these lists to be able to expand if required. that's why I want
to avoid manually entering Value#s in the CHOOSE function that is
used in the Data Validation formula.

The end product will simplify task reporting from remote worksites
where a site supervisor can specify a system, tasks and several layers
of subtasks
 
Yes Biff, I am building on the help you previously provided in another
thread. It seems I seldom come op with a simple task :)

It is a series of drop down lists I'm using the Fruit and Veg examples
to work out the details before I incorporate my data.
On an Entry sheet Data Validation is used to establish category and
subcategory choices. The subcategories are dependent and cascade on
each other.

On a worksheet "Cat1Lists"; Cat1List, FruitList and VegetableList
reside as column headings for named ranges.
Cat1List includes Fruit and Vegetables. fruitlist and vegetablelist
have fruits and vegetables...
On sheet "FruitLists" there are lists such as AppleList and PearList
containing varieties of the respective fruit.
there will be a similar sheet "vegetableLists"

I need these lists to be able to expand if required. that's why I want
to avoid manually entering Value#s in the CHOOSE function that is
used in the Data Validation formula.

CHOOSE(MATCH(A2&"List",ListRng),FruitList,VegetableList) is the Data
Validation Formula used in the first subcatagory.

The end product will simplify task reporting from remote worksites
where a site supervisor can specify a system, tasks and several layers
of subtasks
 
See this:

http://contextures.com/xlDataVal02.html

--
Biff
Microsoft Excel MVP


Yes Biff, I am building on the help you previously provided in another
thread. It seems I seldom come op with a simple task :)

It is a series of drop down lists I'm using the Fruit and Veg examples
to work out the details before I incorporate my data.
On an Entry sheet Data Validation is used to establish category and
subcategory choices. The subcategories are dependent and cascade on
each other.

On a worksheet "Cat1Lists"; Cat1List, FruitList and VegetableList
reside as column headings for named ranges.
Cat1List includes Fruit and Vegetables. fruitlist and vegetablelist
have fruits and vegetables...
On sheet "FruitLists" there are lists such as AppleList and PearList
containing varieties of the respective fruit.
there will be a similar sheet "vegetableLists"

I need these lists to be able to expand if required. that's why I want
to avoid manually entering Value#s in the CHOOSE function that is
used in the Data Validation formula.

CHOOSE(MATCH(A2&"List",ListRng),FruitList,VegetableList) is the Data
Validation Formula used in the first subcatagory.

The end product will simplify task reporting from remote worksites
where a site supervisor can specify a system, tasks and several layers
of subtasks
 
That's where I started but ran into trouble with the indirect function
which you helped me resolve in the other post:
http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/5e6eb3808c3207db?hl=en
I was able to use the choose function in place of indirect as you
recommended but need to make the values dynamic. Because they are
individual values and not an array (cannot reference a range) It looks
as if that is not possible.
The Contextures site example puts me back onto the indirect function
method So I feel like I'm in a loop. However, there are some other
methods on the site, Im going to look at incorporating them.

Thanks again for all your help
Robert
 
That's where I started but ran into trouble with the indirect function
which you helped me resolve in the other post:
http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/5e6eb3808c3207db?hl=en
I was able to use the choose function in place of indirect as you
recommended but need to make the values dynamic. Because they are
individual values and not an array (cannot reference a range) It looks
as if that is not possible.
The Contextures site example puts me back onto the indirect function
method So I feel like I'm in a loop. However, there are some other
methods on the site, Im going to look at incorporating them.

Thanks again for all your help
Robert
 
When you have more than a "couple" of dependent lists it's all about how
clever you are when building the hierarchal sources for lists. It can be a
real challenge!

--
Biff
Microsoft Excel MVP


That's where I started but ran into trouble with the indirect function
which you helped me resolve in the other post:
http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/5e6eb3808c3207db?hl=en
I was able to use the choose function in place of indirect as you
recommended but need to make the values dynamic. Because they are
individual values and not an array (cannot reference a range) It looks
as if that is not possible.
The Contextures site example puts me back onto the indirect function
method So I feel like I'm in a loop. However, there are some other
methods on the site, Im going to look at incorporating them.

Thanks again for all your help
Robert
 
Back
Top