FREQUENCY and INDIRECT…

[How_Do_I]

I have a workbook that looks at the last 5 football seasons and gives me answers!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Anyway as this season is about to finish I need to add those results to my book and drop a season so that I keep my 5 years of data.
<o> </o>
I’ve been playing around with this book for months now and I’ve just about got it to work apart from the following.
<o> </o>
It’s reliant on named ranges so I need to remove a range called Teams05 (2005) and replace it with Teams (2010) for example.
<o> </o>
So if you can imagine this, the current layout of my data is blocks of columns by year on a single sheet…
<o> </o>
2009-2010 2008-2009 2007-2008 2006-2007 2005-2006
<o> </o>
I’m about to obliterate 2005 – 2006 and “over-write” that data with 2010-2011
<o> </o>
All I then want to do is remove the named ranges specific to 2005-2006 and replace them with 2010-2011 ranges.
<o> </o>
Basically I’m using these formula from another book that is working but I can’t get my INDIRECT correct.
<o> </o>
{=SUM(IF(FREQUENCY(IF(Teams<>"",MATCH("~"&Teams,Teams&"",0)),ROW(Teams)-ROW(A2)+1),1))} is working…
<o> </o>
I’ve replaced that with…
<o> </o>
{=SUM(IF(FREQUENCY(IF(INDIRECT(C29)<>"",MATCH("~"&INDIRECT(C29),INDIRECT(C29)&"",0)),ROW(INDIRECT(C29))-ROW(INDEX(INDIRECT(C29),1))+1),1))} which is working…
<o> </o>
I can’t get this one working…
<o> </o>
=IF(ROWS(D$2:D2)<=$C$2,INDEX(Teams,MATCH(SMALL(IF(Teams<>"",IF(ISNA(MATCH(Teams,$D$1:D1,0)),Range)),1),Range,0)),"")
<o> </o>
I need to replace “Teams” with “INDIRECT(C29). Range in the above refers to
<o> </o>
=MMULT((IF(Teams<>"",Teams)>TRANSPOSE(IF(Teams<>"",Teams)))+0,ROW(Teams)^0)
<o> </o>
I’m also not sure about the following amendment to Range…
<o> </o>
=MMULT((IF(INDIRECT(B29)<>"",INDIRECT(B29))>TRANSPOSE(IF(INDIRECT(B29)<>"",INDIRECT(B29))))+0,ROW(INDIRECT(B29))^0)
<o> </o>
Any ideas please anyone?

 

[How_Do_I]

I’ll simplify my question…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
This formula is working…
<o> </o>
{=IF(ROWS(C$4:C4)<=$B$3,INDEX(Home09,MATCH(SMALL(IF(Home09<>"",IF(ISNA(MATCH(Home09,$C$3:C3,0)),Range)),1),Range,0)),"")}
<o> </o>
Range refers to:
<o> </o>
=MMULT((IF(Home09<>"",Home09)>TRANSPOSE(IF(Home09<>"",Home09)))+0,ROW(Home09)^0)
<o> </o>
In both of those formula how would I replace “Home09” with INDIRECT(C29) please? Using Edit>Replace won't work

 

[T. Valko]

I’ll simplify my question…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
This formula is working…
<o></o>
{=IF(ROWS(C$4:C4)<=$B$3,INDEX(Home09,MATCH(SMALL(IF(Home09<>"",IF(ISNA(MATCH(Home09,$C$3:C3,0)),Range)),1),Range,0)),"")}
<o></o>
Range refers to:
<o></o>
=MMULT((IF(Home09<>"",Home09)>TRANSPOSE(IF(Home09<>"",Home09)))+0,ROW(Home09)^0)
<o></o>
In both of those formula how would I replace “Home09” with INDIRECT(C29) please? Using Edit>Replace won't work

If the named range Home09 is a dynamic range defined using functions like OFFSET then INDIRECT won't work.

 

[How_Do_I]

Hello there... No, Home09 ='All Seasons'!$C$30:$C$581

 

[T. Valko]

Hello there... No, Home09 ='All Seasons'!$C$30:$C$581

This worked for me...

Convert the formula to a text string. I did that by inserting a single quote to the front of the formula like this:

'=IF(ROWS(C$4:C4)<=$B$3....

Then Edit>Replace worked.

After the change simply remove the single quote and array enter this formula.

 

[How_Do_I]

When I try to enter I get the warning box "Your formula contains a typed error"... It points to the part I've highlight and underlined...

=IF(ROWS(C$4:C4)<=$B$3,INDEX(INDIRECT(C29),MATCH(SMALL(IF(INDIRECT(C29)<>"",IF(ISNA(MATCH(INDIRECT(C29),$C$3:C3,0)),Range)),1),Range,0)),"")

 

[T. Valko]

When I try to enter I get the warning box "Your formula contains a typed error"... It points to the part I've highlight and underlined...

=IF(ROWS(C$4:C4)<=$B$3,INDEX(INDIRECT(C29),MATCH(SMALL(IF(INDIRECT(C29)<>"",IF(ISNA(MATCH(INDIRECT(C29),$C$3:C3,0)),Range)),1),Range,0)),"")

Well, that's a separate issue!

I see you're using Excel 2003. That formula exceeds the nested function limit of 7.

 

[How_Do_I]

I don't get that sorry, this formula is working...

{=IF(ROWS(C$4:C4)<=$B$3,INDEX(Home09,MATCH(SMALL(IF(Home09<>"",IF(ISNA(MATCH(Home09,$C$3:C3,0)),Range)),1),Range,0)),"")}

How does trying to replace Home09 with INDIRECT(C29) take it over the 7?

I thought the nested function was to do with the IFs?

 

[How_Do_I]

With you now Biff, I've looked at the nested 7 functions... Thanks friend, I have an idea now.

 

[T. Valko]

I don't get that sorry, this formula is working...

{=IF(ROWS(C$4:C4)<=$B$3,INDEX(Home09,MATCH(SMALL(IF(Home09<>"",IF(ISNA(MATCH(Home09,$C$3:C3,0)),Range)),1),Range,0)),"")}

How does trying to replace Home09 with INDIRECT(C29) take it over the 7?

I thought the nested function was to do with the IFs?

That's what a lot of people think.

The nested function level limit applies to all formulas, it has nothing to do with a specific function. It's all functions.

In Excel 2007 the nested function limit was increased to 64.