Simple array question

[excelmacro]

I have an array

=SUM((CostCenter=$A$3)*(Month=F$4)*(Prime=$A$7)*Cost)/1000

Where:
Cost =Monthly_Actuals!$F$2:$F$20000
CostCenter =Monthly_Actuals!$A$2:$A$20000
Month =Monthly_Actuals!$C$2:$C$20000
Prime =Monthly_Actuals!$D$2:$D$20000

I was just wondering if I could instead of refer to cells such as $A$3 (CostCenter=$A$3) have the referance be to a user inputbox and have the user select this information.

 

[Aladin Akyurek]

On 2002-04-26 07:15, excelmacro wrote:
I have an array

=SUM((CostCenter=$A$3)*(Month=F$4)*(Prime=$A$7)*Cost)/1000

Where:
Cost =Monthly_Actuals!$F$2:$F$20000
CostCenter =Monthly_Actuals!$A$2:$A$20000
Month =Monthly_Actuals!$C$2:$C$20000
Prime =Monthly_Actuals!$D$2:$D$20000

I was just wondering if I could instead of refer to cells such as $A$3 (CostCenter=$A$3) have the referance be to a user inputbox and have the user select this information.

You mean to set the condition to a selection made a user. Absolutely.

You can also use a SUMPRODUCT formula which does not require entering using control+shift+enter and an edit:

=SUMPRODUCT((CostCenter=$A$3)*(Month=F$4)*(Prime=$A$7)*Cost)/1000

Aladin

 

[excelmacro]

how would i go about doing this? i only need the user to select cost center and month.

 

[Aladin Akyurek]

And, another thing, if I may:

You apparently selected the ranges of interest and named them directly, like with Cost which refers to:

Monthly_Actuals!$F$2:$F$20000

I'd suggest using dynamic name range, using the method I described to you in another recent thread. This way you'd have a bit more efficient workbook.
This message was edited by Aladin Akyurek on 2002-04-26 09:40

 

[excelmacro]

whoops i kinda didn't read all that you wrote there. Right now i just have the array linking to cells where the cost center, month data is (prime and cost also, but i don't want that to change). I want the user to select the cost center and month. I am also having problems with that list data you gave me. I can't get the match formula to work. all i get is #NA, and its from the 9.9999999999999E+307 i suspect.

 

[Aladin Akyurek]

On 2002-04-26 07:24, excelmacro wrote:
how would i go about doing this? i only need the user to select cost center and month.

Let the formula

=SUMPRODUCT((CostCenter=$A$3)*(Month=F$4)*(Prime=$A$7)*Cost)/1000

refer to a cell X where the user selects from a list of cost centers instead of A3 and to a cell Y where the user selects a month number or name (whichever appropriate) instead of to F4.

X and Y are cells where you set up say a dropdown list using data validation.

Aladin

 

[Aladin Akyurek]

On 2002-04-26 08:51, excelmacro wrote:
whoops i kinda didn't read all that you wrote there. Right now i just have the array linking to cells where the cost center, month data is (prime and cost also, but i don't want that to change). I want the user to select the cost center and month. I am also having problems with that list data you gave me. I can't get the match formula to work. all i get is #NA, and its from the 9.9999999999999E+307 i suspect.

You suspect wrong. Applied to a numeric column the MATCH formula will return the row number of the last cell in use.

Just type a few numbers in a clean sheet in column A.

In B1 enter:

=MATCH(9.99999999999999E+307,A:A)

and examine the result it returns. When you get this, please return to that reply where I suggested using such formula and try to implement the proposed scheme.

Aladin