i have 1000 workers data base with names, pfnos which is unique. i have to pay salary at diff dates to diff people. i have 12 sheets jan,feb ..
in each sheet when i pay salary and entry is made in the sheet , it should automatically show 50 pd , 950 remains. if another 100 paid on another date ,150 paid 850 remains , how to do it
There may be several possible ways to create a summary.
One way would be to create a master summary sheet showing all twelve months and a table showing how many workers, how many paid and how many remain.
You can count number of entries from each monthly sheet and show that value on master summary sheet.
If you can attach a sample workbook, someone will be able to understand your concept and be able to help you out.
modytrane
Running totals require you have that starting number somewhere. I should tell you, there is a very simple formula that will sum numbers for a matching ID across multiple sheets, but it requires that you name your sheets in a numerically sequential way...like Month1, Month2, etc...instead of Jan, Feb.
If you're willing to do that, you won't have to do much other heavy lifting in your sheet to accomplish what you want.
=SUM(SUMIF(INDIRECT("Month"&{1,2,3,4,5,6,7,8,9,10,11,12}&"!A1:A1000"),"="&A2,INDIRECT("Month"&{1,2,3,4,5,6,7,8,9,10,11,12}&"!C1:C1000")))
This formula searches sheets Month1-Month12!A1:A1000 for a particular ID (A2 from the master sheet in this example)...and for every line that matches, it adds the value found in column C in all of those sheets.
So you can adjust those column references to match your own layout. Attached is a working sample.
This has the benefit of being simple. If you are not willing/allowed to adjust the names of your sheets to something sequentially numerical like this, then you will have to use a much more complicated approach...of which there are plenty of options.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks