Removing quotes generated in formula

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

Robert H

Ive isolated a problem in a large formula to this:
=COUNTA(FruitList) works

=COUNTA("FruitList") does not work

I build FruitList by:
COUNTA($A$2&"List") which puts FruitList in quotes as can be seen in
Formula Evaluator

I tried using substitute in the results but am just hacking it up.

how can I get the name FruitList constructed without quotes
thanks
Robert
 
Try it like this:

=COUNTA(INDIRECT(A2&"List"))

Note that this will not work if FruitList is a dynamic range defined using
functions like OFFSET.
 
Biff
It is a dynamic range and I get a #REF! from INDIRECT(A2&"List").
It looks there are limitations to referencing a dynamic range that I
was not aware of. If there is no way to "fix" this (remove the quotes)
Ill have to rethink my overall approach.

Thanks
Robert
 
The problem is with the INDIRECT function. It can only take arguments that
resolve to text representations of a valid reference. A named dynamic range
doesn't meet that condition if the formula used to define the range returns
an array of the range values.

INDIRECT is very nuanced when it comes to building ranges like this.

An alternative is to use the CHOOSE function but it depends on what you're
trying to do. Here's an example of how to use it:

List the names of your named ranges in a range of cells:

F1 = FruitList
F2 = VegList
F3 = MeatList

Then, you can use a formula like this:

=COUNTA(CHOOSE(MATCH(A2&"List",F1:F3,0),FruitList,VegList,MeatList))

The drawback to this is that CHOOSE is limited to the number of ranges you
can reference depending on which verson of Excel you're using.

--
Biff
Microsoft Excel MVP


Biff
It is a dynamic range and I get a #REF! from INDIRECT(A2&"List").
It looks there are limitations to referencing a dynamic range that I
was not aware of. If there is no way to "fix" this (remove the quotes)
Ill have to rethink my overall approach.

Thanks
Robert
 
try a defined name
insert>name>define>name it>put your formula in the "refers to" box. Test by
f5>goto and type in the name you gave it.
 
Thanks for the repy Don,
FruitList is a defined name but it refers to a dynamic range.
I don't understand "Test by f5>goto and type in the name you gave it."
do you mean put =f5>goto in a cell? where do I type the name?
pardon my ignorance...
 
Don,
Fruitlist is a defined name and it does reference a dynamic range.
using F5 is interesting, thanks for the tip. Some of the named ranges
on the sheet do not show up in the list. Fruitlist does not show up!
does this mean it is not valid? or not referable? When I click in the
names formula for FruitList the correct range of cells are outlined.

thanks
Robert
 
Biff, I experimented with your sugestion.

Modified the formula to match my current names
=COUNTA(CHOOSE(MATCH(A2&"List",F1:F2,0),FruitList,VegetableList))

F1 = FruitList
F2 = VegetableList

Note - VegetableList is currently a simple reference to the range in
the "refers to" block of the name.

I tried looking at A2 (fruit) and A3 (veg) in the match function.
however count always returns a one.
the formula progresses as so:
=COUNTA(CHOOSE(Match("FruitList",F1:F2,0,FruitList,VegetableList))
=COUNTA(CHOOSE(#NA,FruitList,VegetableList))
=COUNTA(#NA)

it seems like the match function is not matching the lookup value with
either cell in the array

Thank you
Robert
 
Biff, I experimented with your sugestion.

Modified the formula to match my current names
=COUNTA(CHOOSE(MATCH(A2&"List",F1:F2,0),FruitList,VegetableList))

F1 = FruitList
F2 = VegetableList

Note - VegetableList is currently a simple reference to the range in
the "refers to" block of the name.

I tried looking at A2 (fruit) and A3 (veg) in the match function.
however count always returns a one.
the formula progresses as so:
=COUNTA(CHOOSE(Match("FruitList",F1:F2,0,FruitList,VegetableList))
=COUNTA(CHOOSE(#NA,FruitList,VegetableList))
=COUNTA(#NA)


I cheated by defining the lookup values and it works
=COUNTA(CHOOSE(MATCH(A2&"List",{"Fruitlist","VegetableList"},
0),FruitList,VegetableList))

So I think its a mater of format of the lookup value and the lookup
array ive been tweaking the F1 and F2 cells but have not got anywhere
yet.

Thank you
Robert

****
 
Here's a small sample file that demonstrates this:

xCHOOSE.xls 14kb

http://cjoint.com/?fAdKqDfxBO

H2:H20 is a dynamic range named FruitList
I2:I20 is a dynamic range named VegetableList

The ranges are defined as being contiguous blocks of data (no empty cells
between entries).

A2 = drop down = either Fruit or Vegetable

A6 = formula:

=COUNTA(CHOOSE(MATCH(A2&"List",H1:I1,0),FruitList,VegetableList))

--
Biff
Microsoft Excel MVP


Biff, I experimented with your sugestion.

Modified the formula to match my current names
=COUNTA(CHOOSE(MATCH(A2&"List",F1:F2,0),FruitList,VegetableList))

F1 = FruitList
F2 = VegetableList

Note - VegetableList is currently a simple reference to the range in
the "refers to" block of the name.

I tried looking at A2 (fruit) and A3 (veg) in the match function.
however count always returns a one.
the formula progresses as so:
=COUNTA(CHOOSE(Match("FruitList",F1:F2,0,FruitList,VegetableList))
=COUNTA(CHOOSE(#NA,FruitList,VegetableList))
=COUNTA(#NA)

it seems like the match function is not matching the lookup value with
either cell in the array

Thank you
Robert
 
Although defined names do not show up on the list it does not mean they are
not good. I always use the f5 method to test the range before applying.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don,
Fruitlist is a defined name and it does reference a dynamic range.
using F5 is interesting, thanks for the tip. Some of the named ranges
on the sheet do not show up in the list. Fruitlist does not show up!
does this mean it is not valid? or not referable? When I click in the
names formula for FruitList the correct range of cells are outlined.

thanks
Robert
 
Back
Top