Excel (Formula/VBA) Help: Two sheets into one

Rob_010101

Board Regular
Joined
Jul 24, 2017
Messages
198
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have an absence tracker which records absence in a current 6-month rolling period and a previous 6-month rolling period.

Each occasion of absence is recorded as a separate row and absence is manually added to the "current 6 month" sheet daily (as it occurs). A piece of VBA code then looks at the absence start date and if it falls before 6 months from the current day, it would automatically move the whole row to the next blank row on the "previous 6 months" sheet. It also looks at the "previous 6 months" sheet and any rows with an absence start date less than 12 months from today will go to the "previous 12-24 months" sheet and will then be automatically deleted from 12-24 months, if more than 24 months old.

Each occasion of absence is recorded on its own row, with the unique employee ID in column A (which uses the REPT formula to get leading 0's in employee ID from column I). So, if an employee was sick on 05/05/2022 for 1 day and then sick again on 06/06/2022, the employee would have 2 rows. Example:

1666970940183.png


I need a separate sheet to tell me how many occasions of absence each employee has over the past 12 months (i.e. over the two sheets "current 6 months" and "previous 6 months"), so an "Occasions last 12 months" sheet.

1666971068075.png


As per picture above, I did this for looking at only current 6 months using =Unique(Filter(XXXX,XXXX<>"")) on employee ID to pull a list of unique values, then used COUNTIF to count how many times the employee ID appeared in "current 6 months", which looks like this:

1666971636086.png


BUT this method won't work when the data is split over 2 sheets. Not sure if this can be formula driven or needs VB Code.

Kind Regards
 
Last edited:
Hello

I'm trying

Excel Formula:
=IF(B2<>0, MAXIFS('Current 6 months'!J:J, 'Current 6 months'!A:A, A2)+MAXIFS('Previous 6 months'!J:J, 'Previous 6 months'!A:A, A2), "")

To look over the two sheets to get the "latest absence start date" but it's returning weird results:

1667212415734.png


Appreciate you've helped me a lot, so I can raise on another thread if you've got bored of me!

Regards
Chris
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hello

I'm trying

Excel Formula:
=IF(B2<>0, MAXIFS('Current 6 months'!J:J, 'Current 6 months'!A:A, A2)+MAXIFS('Previous 6 months'!J:J, 'Previous 6 months'!A:A, A2), "")

To look over the two sheets to get the "latest absence start date" but it's returning weird results:

View attachment 77414

Appreciate you've helped me a lot, so I can raise on another thread if you've got bored of me!

Regards
Chris
Never bored Chris, but I'm finished for tonight. If you want a quick answer, best to start a new thread.
 
Upvote 0
Never bored Chris, but I'm finished for tonight. If you want a quick answer, best to start a new thread.
This got solved

Excel Formula:
=IF(OR(ISBLANK(B2)), "", IF(A2<>0,MAX(MAXIFS('Current 6 months'!J:J,'Current 6 months'!A:A,A2),MAXIFS('Previous 6 months'!J:J,'Previous 6 months'!A:A,A2)),""))

The IF(OR(ISBLANK was added later

Have a good evening!
Many Thanks
 
Upvote 0

Forum statistics

Threads
1,224,737
Messages
6,180,656
Members
452,992
Latest member
TokugawaIesuma

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top