Is this possible? What's the best way?

[Eltis]

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.

[Aladin Akyurek]

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 ]

[s-o-s]

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.

[Eltis]

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.

[Eltis]

Got it! Thank you so much!!!