How_Do_I
Well-known Member
- Joined
- Oct 23, 2009
- Messages
- 1,843
- Office Version
- 2010
- Platform
- Windows
I have a workbook that looks at the last 5 football seasons and gives me answers!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><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?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><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?
