Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Simple array question

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Alberta, Canada
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Alberta, Canada
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Alberta, Canada
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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


  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,034
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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