Array not working correctly Returns FALSE on second part
 

The below array I am working on is having a problem with the scond part. The
cell is returning false. The first part works correctly when "All" is
selected in my list box which triggers the first part, but when I select a
specific region, the cells returns FALSE. Usually the array just doesn't
work if there is a problem so I am not sure how to interpret this. I have
checked the standard items that can cause problems with arrays.
Specifically, name range size are equal. Thanks


=IF('Dashboard-Sales'!$A$6="ALL",SUM(IF($AM3&Key!$B$2&Lists!$I$3= Win_Probability&CloseMonth&C_F_U_R__Blank,Total_Co ntract),SUM(IF($AJ3&'Dashboard-Sales'!$A$6&Key!$B$2&Lists!$I$3=Win_Probability&Re gion&CloseMonth&C_F_U_R__Blank,Total_Contract))))

Perhaps something along these lines
is close to what you're trying to do
(using named ranges, and INDIRECT
to concat cell contents to return named ranges, etc ..)

Assuming you have 3 named ranges:

Win_ProbabilitynCloseMonthnC_F_U_R__Blank
Lose_ProbabilitynCloseMonthnC_F_U_R__Blank
Total_Contract

referring to, respectively :

='Dashboard-Sales'!$B$6:$B$10
='Dashboard-Sales'!$C$6:$C$10
='Dashboard-Sales'!$D$6:$D$10

Note: I couldn't use "&" in the named ranges,
looks like its an invalid character. Replaced it with an "n".

And in sheet: Dashboard-Sales, in B6:D10
you have the data below:

100 200 100
200 100 200
200 100 200
200 100 200
100 200 100

Other settings/contents assumed:

In 'Dashboard-Sales'!$A$6: ALL
In Key!$B$2: _Probability
In Lists!$I$3: nCloseMonthnC_F_U_R__Blank

Finally in sheet: Summary (say), you have

In AM3: Win
In AJ3: Lose

With the set-up above,
the array formula below in say, AN3

=IF('Dashboard-Sales'!$A$6="ALL",SUM(IF(INDIRECT($AM3&Key!$B$2&Li sts!$I$3)=1
00,Total_Contract)),SUM(IF(INDIRECT($AJ3&'Dashboar d-Sales'!$A$6&Key!$B$2&Lis
ts!$I$3)=100,Total_Contract)))

will evaluate to 200
(If 'Dashboard-Sales'!$A$6 contains: ALL)

or, If 'Dashboard-Sales'!$A$6 is cleared (empty)
the array formula will then evaluate to 600

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"aaronm49" wrote in message
...
The below array I am working on is having a problem with the scond part.
The
cell is returning false. The first part works correctly when "All" is
selected in my list box which triggers the first part, but when I select a
specific region, the cells returns FALSE. Usually the array just doesn't
work if there is a problem so I am not sure how to interpret this. I have
checked the standard items that can cause problems with arrays.
Specifically, name range size are equal. Thanks



=IF('Dashboard-Sales'!$A$6="ALL",SUM(IF($AM3&Key!$B$2&Lists!$I$3= Win_Probabi
lity&CloseMonth&C_F_U_R__Blank,Total_Contract),SUM (IF($AJ3&'Dashboard-Sales'
!$A$6&Key!$B$2&Lists!$I$3=Win_Probability&Region&C loseMonth&C_F_U_R__Blank,T
otal_Contract))))