Help... How to populate columns (formula, macro, ???)

[vikki123us]

I've attached a sample spreadsheet. But basically what I have to do is get a total by client id for the amount of invoices on a monthly basis and populate them into the correct tab and column. The problem is that a client can have multiple lines per month and I just need a total by client id by month. Invoices are produced almost on a daily basis through out the month. I update a master list at the end of the month..

any idea how or what I should do?

Master List:

Client Id	Segment	Client Name	Plan Id	Plan Name	Internal/External	Plan Type	Invoice Type	Bill Cycle	Period Ending	Billing Date	Billing Month	Invoice Number	Rev	Invoice Status	Approval Status	Level	Level Code	Description	Fee Category (If Appl.)	Fee Service (If Appl.)	Payment Method	Activity Type	Invoice Amount	Charge Quantity	Invoice Description	Invoice Comment	Source	Stat Period	Stat Range
4515	a	Jane Smith	220							01/01/2013		1	0										-200
4515	a	Jane Smith	121							01/01/2013		1	0										-200
7845	a	John Doe	25631							01/02/2013		2	0										-120
7845	a	John Doe	45158							01/02/2013		2	0										-120
2159	a	Help Me	48935							01/03/2013		3	0										-50
2518	a	Client Name A	59874							01/04/2013		4	0										-650
2518	a	Client Name A	5462							01/04/2013		4	0										-650
2518	a	Client Name A	124							01/04/2013		4	0										-650
89463	a	Client Name B	2356							01/05/2013		5	1										-125
89463	a	Client Name B	2568							01/05/2013		5	1										-125
74523	b	Client Name C	28465							01/06/2013		6	2										-800
5216	b	Joe Smith	596							01/07/2013		7	0										-450
5216	b	Joe Smith	251							01/07/2013		7	0										-450
1204	b	Adam Tree	54635							01/08/2013		8	0										-630
8596	c	Jane Doe	1234							01/09/2013		9	0										-120
44845	c	I don't know	1							01/10/2013		10	1										-500
95632	c	new client	784							01/11/2013		11	1										-800
87542	a	needs to be added	5941							01/25/2013		12	1										-100

<tbody>
</tbody>

Invoiced Annually Tab:

Client ID

Segment

Plan ID

Bill Cycle

Type

Frequency

Client Name

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

5216

Joe Smith

1204

Adam Tree

8596

Jane Doe

<tbody>
</tbody>

Invoiced Quarterly TAb:

Client ID

Segment

Plan ID

Bill Cycle

Type

Frequency

Client Name

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

2518

Client Name A

89463

Client Name B

74523

Client Name C

<tbody>
</tbody>

 

[alan.m]

Have you considered Pivot Tables or SubTotal?