I am working in Xcelsius 2008 and I have created about 100 defined names. These refer to cells on Sheet2 that create ranges. I am having an issue calling the range as it gets returned as text.
E.G.:
I am using the following to build a range based on where the column header is found:
="Dollars!$"&LEFT(ADDRESS(1,COLUMN(INDEX(Dollars!$A$1:$BZ$1,MATCH(""&$F6&"",Dollars!$A$1:$BZ$1,0))),4),FIND(1,ADDRESS(1,COLUMN(INDEX(Dollars!$A$1:$BZ$1,MATCH(""&$F6&"",Dollars!$A$1:$BZ$1,0))),4))-1)&"$1:$"&LEFT(ADDRESS(1,COLUMN(INDEX(Dollars!$A$1:$BZ$1,MATCH(""&$F6&"",Dollars!$A$1:$BZ$1,0))),4),FIND(1,ADDRESS(1,COLUMN(INDEX(Dollars!$A$1:$BZ$1,MATCH(""&$F6&"",Dollars!$A$1:$BZ$1,0))),4))-1)&"$500"
Which gives me a range of Dollars!$A$1:$A$500 mapped to a defined name of Brand.
I then use the following to create the name that gets referenced in my formula:
=INDEX(FormulaRanges!$F$6:$G$100,MATCH(""&FormulaRanges!$B$4&TEXT(Config!$C$6,1)&"",FormulaRanges!$F$6:$F$100,0),2)
Which gives me a name similar to NetWTD, NetPTD, etc. depending on what is given in the Config!$C$6 cell.
This name is then used in an array formula like the following:
=ROUND(SUM(IF(Brand="Brand", NETWTD, 0)
The Indirect function would normally suite my puposes here, but it is not supported by Xcelsius. Any ideas how I can call this and have Brand returned as my range instead of a string? Alternatively, I'm open to more efficient ways of creating/calling the ranges as well. Thanks in advance!
E.G.:
I am using the following to build a range based on where the column header is found:
="Dollars!$"&LEFT(ADDRESS(1,COLUMN(INDEX(Dollars!$A$1:$BZ$1,MATCH(""&$F6&"",Dollars!$A$1:$BZ$1,0))),4),FIND(1,ADDRESS(1,COLUMN(INDEX(Dollars!$A$1:$BZ$1,MATCH(""&$F6&"",Dollars!$A$1:$BZ$1,0))),4))-1)&"$1:$"&LEFT(ADDRESS(1,COLUMN(INDEX(Dollars!$A$1:$BZ$1,MATCH(""&$F6&"",Dollars!$A$1:$BZ$1,0))),4),FIND(1,ADDRESS(1,COLUMN(INDEX(Dollars!$A$1:$BZ$1,MATCH(""&$F6&"",Dollars!$A$1:$BZ$1,0))),4))-1)&"$500"
Which gives me a range of Dollars!$A$1:$A$500 mapped to a defined name of Brand.
I then use the following to create the name that gets referenced in my formula:
=INDEX(FormulaRanges!$F$6:$G$100,MATCH(""&FormulaRanges!$B$4&TEXT(Config!$C$6,1)&"",FormulaRanges!$F$6:$F$100,0),2)
Which gives me a name similar to NetWTD, NetPTD, etc. depending on what is given in the Config!$C$6 cell.
This name is then used in an array formula like the following:
=ROUND(SUM(IF(Brand="Brand", NETWTD, 0)
The Indirect function would normally suite my puposes here, but it is not supported by Xcelsius. Any ideas how I can call this and have Brand returned as my range instead of a string? Alternatively, I'm open to more efficient ways of creating/calling the ranges as well. Thanks in advance!
