Is this possible? What's the best way?
Is this possible?  What's the best way?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Is this possible? What's the best way?

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi, here is the problem:

    I am creating a budget spreadsheet. The user needs to fill in fields for vendor, GL (budget) code, and amount.

    I am trying to make the GL codes into a drop down list. What complicates this is that I want the amount to be grouped and summed by the drop list selections into another part of the sheet.

    To clarify with example...Say there are 3 entries to be made. There are 4 GL codes total, ie: 1,2,3,4. My data to be input is as follows (vendor, GL, amount):

    Office Max, 2, $5
    Home Depot, 1, $2
    Staples, 2, $6

    Note again that the GL codes (the second criteria in the data example) are drop down choices. Somewhere along the bottom of the sheet I want a summary of costs per GL code. So for GL code 2, I'd get $11 and so forth.

    Upon looking around, it seems I might need to use VB, but I am not very familiar with it yet. Any suggestions would be greatly appreciated.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,790
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-16 14:44, Eltis wrote:
    Hi, here is the problem:

    I am creating a budget spreadsheet. The user needs to fill in fields for vendor, GL (budget) code, and amount.

    I am trying to make the GL codes into a drop down list. What complicates this is that I want the amount to be grouped and summed by the drop list selections into another part of the sheet.

    To clarify with example...Say there are 3 entries to be made. There are 4 GL codes total, ie: 1,2,3,4. My data to be input is as follows (vendor, GL, amount):

    Office Max, 2, $5
    Home Depot, 1, $2
    Staples, 2, $6

    Note again that the GL codes (the second criteria in the data example) are drop down choices. Somewhere along the bottom of the sheet I want a summary of costs per GL code. So for GL code 2, I'd get $11 and so forth.

    Upon looking around, it seems I might need to use VB, but I am not very familiar with it yet. Any suggestions would be greatly appreciated.
    It doesn't look like the GL codes and the associated data are entered again and again from the same cell. If my observation is right, you can use SUMIF instead of ressorting to VBA.

    =SUMIF(Range1,the-GL-code,Range2)

    Range1 is the range where the entered codes are and Range2 is the range where the amounts are.

    Somewhere at the bottom of the sheet make a unique list of the possible GL codes and next to it enter the above formula.

    Hope this helps.

    [ This Message was edited by: Aladin Akyurek on 2002-04-16 15:02 ]

  3. #3
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Eltis,

    You can do this with out VB.

    1st suggestion for the Budget code select the range in the column that this goes into and then do a DataValidation

    Choose list and enter 1,2,3,4 this will enable the drop downs you request.

    For The Summary you refer to

    Lets assume your data as provided is in columns A,B and C
    and goes down from row 2 to 4.

    In the other part of the spreadsheet, use the SUMIF function as follows

    A11 = Bgt Code (Title)
    A12 = 1
    A13 = 2
    A14 = 3
    A15 = 4

    B11 = Amount (Title)
    B12 = =SUMIF($B$2:$B$4,A12,$C$2:$C$4)
    B13 = =SUMIF($B$2:$B$4,A13,$C$2:$C$4)
    B14 = =SUMIF($B$2:$B$4,A14,$C$2:$C$4)
    B15 = =SUMIF($B$2:$B$4,A15,$C$2:$C$4)

    This should give you the info you're requesting.



    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I won't be able to test it before I leave today, but I wanted to give thanks to both of you in advance. Very good explanations, I know exactly what to do. I'll let you know tomorrow how it goes.

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Got it! Thank you so much!!!

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
  •  

 

 
DMCA.com