Is this possible? What's the best way?

Eltis

New Member
Joined
Apr 15, 2002
Messages
21
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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top