Thanks:  0
Likes:  0

1. 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.

2. 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

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

4. 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 ]

5. 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.

6. 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.

7. 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.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•