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

[jedale]

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

 

[Bob Phillips]

=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)

 

[jedale]

Works like a charm.. Thanks

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

Thanks

Jeff