Displaying specific information from one sheet on another

Hi again

We archive lines from our absence report to a single sheet. The sheet with the archive on is massive and getting bigger. But that is beside the point.

I would like to be able to have a second sheet comb through the archive and display all absences between 2 dates (All between Sunday and Saturday) which would include new absences starting, existing absences returning, and all absences that span both dates. Is would need to display the whole line of information (which is around 40 cols I think)

Can anyone help with this?

or this formula that assumes that returns all rows where the start date is in P column and end date in Z column:
Array formula:

Please Login or Register  to view this content.

try this,
run the macro :StartBox
this load a form to enter start and end dates.

the code then cans the list and marks the records (in AQ)
you can then filter on this.

Hello again CharlieBeth,

If indeed Paul is correct in saying that the End Dates are in Column Z, then my code amended as follows should work for you:-

Please Login or Register  to view this content.

I've attached your sample with the above code implemented. Click on the green button to see it work.

Cheerio,
vcoolio.

This is great - I have never touched Macros before and don't know anything about them. It is displaying the people who have returned with ease.

Could this be tweaked to include absences that have not yet returned? Their cell in Z is empty.

Originally Posted by vcoolio

Hello again CharlieBeth,

If indeed Paul is correct in saying that the End Dates are in Column Z, then my code amended as follows should work for you:-

Please Login or Register  to view this content.

I've attached your sample with the above code implemented. Click on the green button to see it work.

Cheerio,
vcoolio.

Hello CharlieBeth,

Could this be tweaked to include absences that have not yet returned? Their cell in Z is empty.

Perhaps this:-

Please Login or Register  to view this content.

Cheerio,
vcoolio,

Hi

Thanks for all your help so far. I think I almost have what I need now.

I have used that CharleBeth.xlsm in this spreadsheet, but not all the absences I am looking for pull through. I'm not sure what I am doing wrong.

I am attaching the spreadsheet I am working on. Tab 1 (All Absences) is all my absences this year. Tab 2 (This Week) is the absences between two dates - thank you for the macro - and tab 3 (Absence Table) is the information downloaded from our Kronos System that I need to marry my tab 2 info with. Currently in Tab 3 I have 12 people Kronos recorded as absent between my two dates which Tab 2 hasn't pulled off Tab 1.

The missing people are there on Tab 1. I cannot see why it's not working for them. Have I botched it?

Hello CharlieBeth,

Try implementing the following code into your file in your last post:-

Please Login or Register  to view this content.

Some of the people (from the "Absence Table" sheet) listed in the "All Absence" sheet won't have their data transferred to the "This Week" sheet as their dates are outside the search parameters in B2 and C2.

Cheerio,
vcoolio.

That's a shame because I need all the people who have had any absence between those dates to transfer including long term sick whose absence start and return dates are well out those dates.

Perhaps we could go at it from another angle?

I have been given a sheet that my colleague uses. That is the one I need to combine with mine.

"wk 34" sheet contains downloads from our T&A system. The one consistent bit of data being the Emp No for each person. The first tab is all our weekly staff. The second tab is a record of absences in the specific week (or any other time period) we are looking at. The third tab is a pivot table my colleague makes showing the number of days each absent employee has taken off work.

"Dashboard" is the information I gather. The same Emp No is used here (which is why we used it for index, vlookup etc). It collects information of where people work - Line and Shift, their length of service and age, and reason for absence.

What we want to be able to do is pull the Line, Shift, Reason for Absence, LOS and Age from the dashboard and display it on the 3rd tab of "wk 34" against the correct person (using the Emp No).

I need to be able to show - for any period of time from a pay week to a financial year if that amount of data is provided on the import - the number of days lost for any particular absence reason, see if there are trends e.g. increased musclioskelial (I can't spell that!) in people with longer lengths of service etc. I have been asked for it to be displayed on the Tab 3 of "Wk 34"


Possible??

Hello CharlieBeth,

That's a shame because I need all the people who have had any absence between those dates to transfer including long term sick whose absence start and return dates are well out those dates.

If we go back to post #6, the code amended as follows should do this for you:-

Please Login or Register  to view this content.

I hope that this helps.

Cheerio,
vcoolio.

It still does not seem to find everyone, such as Emp No 210611 whose absence ran from 03/07/2017 to 22/03/2018

Thank you for all your help :-)