HI Vasant
Sorry for the late reply & thanks you very much for the effort,. Like to tell you that everything is perfect except one change need to do is that when ever Emp taken leaves continuously like say (15th June to 23rdJune ) so when I click for output it should give me only one entry but as per your sheet it gives me 6 entries) In Short: 1) Whenever Emp takes continues leaves (like Mon to Thu , Tue to wed , Mon to Fri ) I want only one entry And *(need to modify code for this)* 2) whenever Emp absent or taken leave on fri & mon then From & to Date will be from Fri to Mon *this is comes properly in output. (no need to change the code)* 3) And if Emp taken leaves or diff dates then from & to dates will be same like (13th June to 13th June) *this is comes properly in output. (no need to change the code)* Rest is fine Request you to kindly make the small change & resent the file Thanks in advance On Sun, Jun 26, 2011 at 11:32 PM, Vasant <vasant...@gmail.com> wrote: > Hi Maulik > > Pls find the file attached > > I have created 3 sheets, in line with your original file. The code reads > the data from sheet1 and populates sheet2 and sheet3 as per your > requirements > > i encourage you to go through the code and understand it, so that you will > be able to make modifications as per your original file. > > Also i would suggest that you use drop down list in sheet1 in the input > area in order to avoid typo errors. Refer Y5 in sheet1. > > Hope this helps ! :) > > > 'Code used > > Sub GetTotals() > Dim DataWkSht As Worksheet, Lrow As Integer, LCol As Integer > Dim StartRow1 As Integer, StartRow2 As Integer > Dim LvDtls As Worksheet > Dim LvSummary As Worksheet > Set LvDtls = ThisWorkbook.Worksheets("Sheet2") > Set LvSummary = ThisWorkbook.Worksheets("Sheet3") > Set DataWkSht = ThisWorkbook.Worksheets("Sheet1") > Lrow = DataWkSht.Range("A65536").End(xlUp).Row > LCol = DataWkSht.Range("IV2").End(xlToLeft).Column > For cntr = 2 To Lrow > LvDtls.Cells(cntr, 1) = DataWkSht.Cells(cntr, 1) > LvDtls.Cells(cntr, 2) = DataWkSht.Cells(cntr, 2) > LvDtls.Cells(cntr, 3) = DataWkSht.Cells(cntr, 3) > For ColCntr = 4 To LCol > If Trim(UCase(DataWkSht.Cells(cntr, ColCntr))) = "IL" Or > Trim(UCase(DataWkSht.Cells(cntr, ColCntr))) = "NCNS" Then > If Weekday(DataWkSht.Cells(1, ColCntr)) = 6 Then > If Trim(UCase(DataWkSht.Cells(cntr, ColCntr + 3))) = "IL" Or > Trim(UCase(DataWkSht.Cells(cntr, ColCntr + 3))) = "NCNS" Then > LvDtls.Cells(cntr, 4) = LvDtls.Cells(cntr, 4) + 4 > LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column + > 1) = Format(DataWkSht.Cells(1, ColCntr), "dd-mmm-yyyy") > LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column + > 1) = Format(DataWkSht.Cells(1, ColCntr + 1), "dd-mmm-yyyy") > LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column + > 1) = Format(DataWkSht.Cells(1, ColCntr + 2), "dd-mmm-yyyy") > LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column + > 1) = Format(DataWkSht.Cells(1, ColCntr + 3), "dd-mmm-yyyy") > > SumRow = LvSummary.Range("A65536").End(xlUp).Row + 1 > LvSummary.Cells(SumRow, 1) = DataWkSht.Cells(cntr, 1) > LvSummary.Cells(SumRow, 2) = DataWkSht.Cells(cntr, 2) > LvSummary.Cells(SumRow, 3) = DataWkSht.Cells(cntr, 3) > LvSummary.Cells(SumRow, 4) = Format(DataWkSht.Cells(1, ColCntr), > "dd-mmm-yyyy") > LvSummary.Cells(SumRow, 5) = Format(DataWkSht.Cells(1, ColCntr + > 3), "dd-mmm-yyyy") > LvSummary.Cells(SumRow, 6) = 4 > > ColCntr = ColCntr + 3 > Else > LvDtls.Cells(cntr, 4) = LvDtls.Cells(cntr, 4) + 1 > LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column + > 1) = Format(DataWkSht.Cells(1, ColCntr), "dd-mmm-yyyy") > > SumRow = LvSummary.Range("A65536").End(xlUp).Row + 1 > LvSummary.Cells(SumRow, 1) = DataWkSht.Cells(cntr, 1) > LvSummary.Cells(SumRow, 2) = DataWkSht.Cells(cntr, 2) > LvSummary.Cells(SumRow, 3) = DataWkSht.Cells(cntr, 3) > LvSummary.Cells(SumRow, 4) = Format(DataWkSht.Cells(1, ColCntr), > "dd-mmm-yyyy") > LvSummary.Cells(SumRow, 5) = Format(DataWkSht.Cells(1, ColCntr), > "dd-mmm-yyyy") > LvSummary.Cells(SumRow, 6) = 1 > > > End If > Else > LvDtls.Cells(cntr, 4) = LvDtls.Cells(cntr, 4) + 1 > LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column + 1) = > Format(DataWkSht.Cells(1, ColCntr), "dd-mmm-yyyy") > > SumRow = LvSummary.Range("A65536").End(xlUp).Row + 1 > LvSummary.Cells(SumRow, 1) = DataWkSht.Cells(cntr, 1) > LvSummary.Cells(SumRow, 2) = DataWkSht.Cells(cntr, 2) > LvSummary.Cells(SumRow, 3) = DataWkSht.Cells(cntr, 3) > LvSummary.Cells(SumRow, 4) = Format(DataWkSht.Cells(1, ColCntr), > "dd-mmm-yyyy") > LvSummary.Cells(SumRow, 5) = Format(DataWkSht.Cells(1, ColCntr), > "dd-mmm-yyyy") > LvSummary.Cells(SumRow, 6) = 1 > > End If > End If > Next ColCntr > Next cntr > End Sub > > > > On Sun, Jun 26, 2011 at 2:10 PM, maulik desai <mauliksde...@gmail.com>wrote: > >> Hi All, >> >> Awaitnig your reply >> >> Kindly revert with the solution. >> >> On Sun, Jun 19, 2011 at 5:42 PM, maulik desai >> <mauliksde...@gmail.com>wrote: >> >>> Hi All, >>> >>> I am having 1 Excel sheet which contains 3 sheets >>> In sheet1 main database i have to update in every month for Leave >>> calculation >>> In Sheet2 I have to find out the total leaves taken in that month with >>> dates of "IL & "NCNS" Staus from Sheet 1 (details given in attached sheet) >>> In sheet3 i have to update the candidates details as per his leave dates >>> (format & details given in attached sheet) >>> Request you to kindly provide the solution with the help of formulas & >>> macro >>> >>> For more details kindly find the attached file. >>> >>> -- >>> Thanks & Regards, >>> Maulik Desai >>> 9967363926 >>> >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like our page on facebook , Just follow below link >>> http://www.facebook.com/discussexcel >>> >> >> >> >> -- >> Thanks & Regards, >> Maulik Desai >> 9967363926 >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> > > > > -- > Regards > > Vasant > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- Thanks & Regards, Maulik Desai 9967363926 -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel