=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)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
Similar Threads
