Changing the text within a function based on a cell reference.

I have multiple defined ranges that I called "List1", "List2"..........
all the way to "List61". I could manually input the new lists names
into the function everytime I fill down but this is a tedious job. I
was wondering if there was a way to have a the function call a seperate
cell to get the number and append it onto the defined range? This way
when I do fill down the List name automatically changes with the
contents of the referenced cell. Is this possible?

Here is the function I am using....
{=SUM(IF(FREQUENCY(IF(LEN(List1)>0,MATCH(List1,List1,0),""),
IF(LEN(List1)>0,MATCH(List1,List1,0),""))>0,1))}
The next in line would be..........
{=SUM(IF(FREQUENCY(IF(LEN(List2)>0,MATCH(List2,List2,0),""),
IF(LEN(List2)>0,MATCH(List2,List2,0),""))>0,1))}

This works fine the way it is but tedious when I want to update.

Here is an attempt to work a solution so far...............
=SUM(IF(FREQUENCY(IF(LEN(List&A31)>0,MATCH(List&A31,List&A31,0),""),
IF(LEN(List&A31)>0,MATCH(List&A31,List&A31,0),""))>0,1))

Where A31 is the number 1,2,3,4,5,6,7.......61.
I get an error with this and if I do the following....

=SUM(IF(FREQUENCY(IF(LEN("List"&A31)>0,MATCH("List"&A31,"List"&A31,0),""),
IF(LEN("List"&A31)>0,MATCH("List"&A31,"List"&A31,0),""))>0,1))

it appends the List with the number like "List29" The problem with this
is that the length function returns a value based on List29 being text
and not a defined range.

Does anybody know of a way to do this?
Thanks in advanced
Jeff

=SUM(IF(FREQUENCY(IF(LEN(INDIRECT("List"&A31))>0,MATCH(INDIRECT("List"&A31),
INDIRECT("List"&A31),0),""),
IF(LEN(INDIRECT("List"&A31))>0,MATCH(INDIRECT("List"&A31),INDIRECT("List"&A3
1),0),""))>0,1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> I have multiple defined ranges that I called "List1", "List2"..........
> all the way to "List61". I could manually input the new lists names
> into the function everytime I fill down but this is a tedious job. I
> was wondering if there was a way to have a the function call a seperate
> cell to get the number and append it onto the defined range? This way
> when I do fill down the List name automatically changes with the
> contents of the referenced cell. Is this possible?
>
> Here is the function I am using....
> {=SUM(IF(FREQUENCY(IF(LEN(List1)>0,MATCH(List1,List1,0),""),
> IF(LEN(List1)>0,MATCH(List1,List1,0),""))>0,1))}
> The next in line would be..........
> {=SUM(IF(FREQUENCY(IF(LEN(List2)>0,MATCH(List2,List2,0),""),
> IF(LEN(List2)>0,MATCH(List2,List2,0),""))>0,1))}
>
> This works fine the way it is but tedious when I want to update.
>
> Here is an attempt to work a solution so far...............
> =SUM(IF(FREQUENCY(IF(LEN(List&A31)>0,MATCH(List&A31,List&A31,0),""),
> IF(LEN(List&A31)>0,MATCH(List&A31,List&A31,0),""))>0,1))
>
> Where A31 is the number 1,2,3,4,5,6,7.......61.
> I get an error with this and if I do the following....
>
> =SUM(IF(FREQUENCY(IF(LEN("List"&A31)>0,MATCH("List"&A31,"List"&A31,0),""),
> IF(LEN("List"&A31)>0,MATCH("List"&A31,"List"&A31,0),""))>0,1))
>
> it appends the List with the number like "List29" The problem with this
> is that the length function returns a value based on List29 being text
> and not a defined range.
>
> Does anybody know of a way to do this?
> Thanks in advanced
> Jeff
>

Works like a charm.. Thanks

I never knew about the indirect function, I will look into it now.

Thanks

Jeff