Huge help needed.

[Helpmeeee]

Here is the template I am trying to modify.
http://office.microsoft.com/en-us/templates/TC011684341033.aspx?pid=CT101441121033

I need a formula to check the Date Posted (Column B), Amount (Column E), and
the GL Code (a range of 1000 in Column A, this is what I'll be changing.
Instead of just 1000, 2000, ect. I will have 1100,1200,2000,2400, ect. so I
need it to check range 1000-1999,2000-2999, with no limit) on the worksheet
named 'itemized expenses' and put the information on the 'Monthly Summary'
worksheet. I would like the example for Column C6 of Monthly Summary (January
of the GL code 1000)

Basically I want excel to see a code say 1200 and put it under the 1000 GL
code on ‘Monthly Expenses’ worksheet, and when it sees 2400, I want it under
2000. When it sees 10200, I want it under the 10000 code. I know it's a lot,
but if there is any way this can be done, it will greatly be appreciated.

 

[JBoulton]

Here's one solution. Put this in C6 on the monthly summary page:

=SUMPRODUCT(--('Itemized Expenses'!$A$2:$A$103<$A8),--('Itemized
Expenses'!$A$2:$A$103>=$A6),--(MONTH('Itemized
Expenses'!$B$2:$B$103)=MONTH('Monthly Expenses Summary'!C$4)),('Itemized
Expenses'!$E$2:$E$103))

Then copy it to the other cells in the rest of the table. You will have to
enter the value of the greatest G/L code in A30.

You can create as many categories as you want in column A. And, they can
cover any range you want. You could use 1000 - 2000 or 6000 - 20000.

This is a quick and dirty approach, but it may get you started.

Best of luck.

Jim

 

[Herbert Seidenberg]

General Ledger
Excel 2007
Used Pivot Tables and Tables.
Fewer and easier formulas:
http://www.savefile.com/files/1839696