Easy entries short procedure required
 

I have the following Sheet1 containing the subscriptions for Provident
Fund Accounts
A B C D E ... N O
1 Name A/c No Jan-yy Feb-yy Mar-yy upto Dec-99
=Sum(C1:M1)
2 Ramesh 5
3 Balu 7
4 Sri Ram 8
5 Venkatesh 9
6 Prakash 13
down upto
300th row

I get monthly subscription of employees with missing information of few
employees say 15 out of 300 employees in excel sheet or I copy the data
from salary programme/ software to Sheet2. They are not in ascending
order either name-wise or account-wise

A B C
1 GPF recoveries for the month of mmm-yy
2 Name A/c.No Amount
3 Balu 7
4 Venkatesh 9
5 Ramesh 5
down upto
285th row

I first sort Sheet2 getting the figures in Col C Account-wise in
ascending order
The I put formula =Vlookup(Sheet2!B2,Sheet2!B3:C285,2) in Sheet1!C2 and
copy it down upto Sheet1!C300 for Jan-yy recoveries and similarly fill
other columns monthly one col and sum row wise at the end of the year

Can anybody suggest a micro-code or Pivot Table or any other short
procedure to avoid repetition of the above work every month

Thanks


--
gandhi318Posted from - http://www.officehelp.in

Easy entries short procedure required
 

The layout of your Sheet1 report is causing you the problem and should be
changed. Store all your data in columnar fashion:

Col A: Name
Col B: A/C #
Col C: Date (or month or however you track this)
Col D: Amount

Each month add the new data to the bottom of the existing data.

When you need to produce your report, generate a Pivot table off the
columnar data.


"gandhi318" wrote:


I have the following Sheet1 containing the subscriptions for Provident
Fund Accounts
A B C D E ... N O
1 Name A/c No Jan-yy Feb-yy Mar-yy upto Dec-99
=Sum(C1:M1)
2 Ramesh 5
3 Balu 7
4 Sri Ram 8
5 Venkatesh 9
6 Prakash 13
down upto
300th row

I get monthly subscription of employees with missing information of few
employees say 15 out of 300 employees in excel sheet or I copy the data
from salary programme/ software to Sheet2. They are not in ascending
order either name-wise or account-wise

A B C
1 GPF recoveries for the month of mmm-yy
2 Name A/c.No Amount
3 Balu 7
4 Venkatesh 9
5 Ramesh 5
down upto
285th row

I first sort Sheet2 getting the figures in Col C Account-wise in
ascending order
The I put formula =Vlookup(Sheet2!B2,Sheet2!B3:C285,2) in Sheet1!C2 and
copy it down upto Sheet1!C300 for Jan-yy recoveries and similarly fill
other columns monthly one col and sum row wise at the end of the year

Can anybody suggest a micro-code or Pivot Table or any other short
procedure to avoid repetition of the above work every month

Thanks


--
gandhi318Posted from - http://www.officehelp.in