Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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)))) |
#2
|
|||
|
|||
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)))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with array equations | Excel Discussion (Misc queries) | |||
Grand Totals @ Same Place | Excel Worksheet Functions | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |