Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|