Removing quotes generated in formula
Robert H
=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
T. Valko
=COUNTA(INDIRECT(A2&"List"))
Note that this will not work if FruitList is a dynamic range defined using
functions like OFFSET.
--
Biff
Microsoft Excel MVP
"Robert H" <robert....@l-3com.com> wrote in message
news:14610ee3-2761-4cd1...@c36g2000yqn.googlegroups.com...
Robert H
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
On May 25, 12:23 am, "T. Valko" <biffinp...@comcast.net> wrote:
> 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
> Microsoft Excel MVP
>
> "Robert H" <robert.hatc...@l-3com.com> wrote in message
T. Valko
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
"Robert H" <robert....@l-3com.com> wrote in message
news:7a25d158-e8bd-42aa...@b1g2000vbc.googlegroups.com...
Don Guillett
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.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"Robert H" <robert....@l-3com.com> wrote in message
news:14610ee3-2761-4cd1...@c36g2000yqn.googlegroups.com...
Robert H
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
On May 25, 1:06 pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> 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.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"Robert H" <robert.hatc...@l-3com.com> wrote in message
Robert H
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
****
On May 25, 12:39 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> 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
>
T. Valko
xCHOOSE.xls 14kb
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
"Robert H" <robert....@l-3com.com> wrote in message
news:3597633d-4864-4678...@z7g2000vbh.googlegroups.com...
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
On May 25, 12:39 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> 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
>
Don Guillett
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
Robert H
file together
Robert
On May 25, 9:42 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> 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
>
> > > > Robert- Hide quoted text -
>
> - Show quoted text -