• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting Sl and UL

dawa

dawa

Member
https://www.dropbox.com/s/mv254n9631hlyr8/Attendance%20sheet.xls


The result i want is when i type the employment no, it extract all the Sl, Ul including its date and week. I.E the whole row with SL and UL.


Any help will be much appreciated.
 
Hi dawa,


On What i want sheet try following:


Cell C12:
Code: 
=SUMPRODUCT(('master file'!D1:K1='what i want'!C18)*('master file'!D216:K216))

Cell C13: =SUMPRODUCT(('master file'!D1:K1='what i want'!C18)*('master file'!D217:K217))


Regards,
 
26 week rolling period attendance record

Week 6 4-Feb-13 SL

Week 6 5-Feb-13 SL

Week 16 13-Apr-13 SL

Week 16 12-Apr-13 SL

Week 18 29-Apr-13 UL

Week 22 27-May-13 SL


I need to extract all of these from the master file when i type only the employment no. So its easy for to figure out the SL,UL on its corresponding date and week.
 
Hello @dawa,

I am not sure I understand your request.


What is the relationship between the employment no and the 26-week rolling period attendance record you posted above?


For the sample data you posted above, can you supply the sample output, and how you derived that sample output manually?


-Sajan.
 
That one on the second sheet is just an example so when we type in the employment it extract all the rows with SL and UL only from the master file.
 
Hi, dawa!

In your uploaded workbook, am I right if I say you should change $376 by $214 in cell references of formulas in range D220:K222?

If so, what to do with references to K239?

Regards!
 
I just need to extract the following

Week 6 4-Feb-13 SL

Week 6 5-Feb-13 SL

Week 16 13-Apr-13 SL

Week 16 12-Apr-13 SL

Week 18 29-Apr-13 UL

Week 22 27-May-13 SL


from my master file in the second sheet when i type in the employment no, 26 month rolling period means, i just need to extract only the rows with SL and UL from the current date upto 26 weeks above, i,e around 182 days.
 
Hi ,


I can certainly help out , but I will take some time ; I'll try to upload your file by tonight , but certainly by tomorrow morning.


Narayan
 
You are always helpful, Thanks for this awesome solution. Still i have something to ask...can we make the formula so that it can only search from the current date upto 190 days above.I.e 26 weeks rolling period. We update the attendance everyday so the formula should look at the latest date and extract all the ones 190 days above not the whole.
 
Hi ,


I think the formulae are doing that ; or at least , it is not using the current date ; instead it is using the last date entered in your data , and looking at 26 weeks prior to that date. This is why data prior to Week 6 will not be considered. Can you recheck ?


Narayan
 
https://www.dropbox.com/s/8q1ntyn0tdwj96m/Attendance%20sheet%20%281%29.xlsx


I have made some common changes and the formula is not working, can you tell me why? and send me the solution too. I have made changes in some of the upper cell and add in new one too. I am very thankful for all your help.
 
Hi ,


Sorry , my mistake. Check the file now :


https://www.dropbox.com/s/ozfbn9l0guvvcbt/Attendance%20sheet_2.xlsx


Narayan
 
I have checked it out but the formula is checking from the top to 180 days, i want the formula to extract from the latest date to 180 days above, i.e if today is the date and so on. it extract all the SL and UL from today to 180 days above.


Thanks again for all these help..... :)
 
https://www.dropbox.com/s/zbzyneqlu8d9xhg/Atten.xls


this is the portion of the data that i have extracted from my origional.....
 
Hi ,


Can you check your file now ?


https://www.dropbox.com/s/ozfbn9l0guvvcbt/Attendance%20sheet_2.xlsx


There are 4 named ranges used : Last_Row , Start_Row , Current_Week and Start_Week.


These need to be created in your workbook if you are not using the uploaded file.


Narayan
 
https://www.dropbox.com/s/tx7ddtvg6tngnl7/Tracker%202012%20-%202013.xls


This is the origional XLS file that i have. I tried but failed to get the answer with these multiple sheets of the file...please help. I want all the sheets, staffs to be included.
 
Hi ,


I think if you take a little trouble to go through all the formulae and find out what the problem is , you will be able to resolve these issues.


The text SDO is appearing because the formulae in column C are using the MATCH function to retrieve information associated with the date ; the date itself is correct , but the information relating to the leave category is being retrieved wrongly because there are 2 dates 12-Apr-13.


If you see the data , B113 should have been 14-Apr-2013 ; instead it is wrongly entered as 12-Apr-2013.


Narayan
 
Hi ,


Sorry , but I have not checked this ; I'll get back to you ; I checked only the problem of SDO appearing in the report.


Narayan
 
You must log in or register to reply here.
Back
Top