Multi-worksheet Complex 3D CountIF Help? VBA Solution?

[Mrmarc]

Hi all,

I think a countif formula is what I require however I am open for suggestions and correction.

attached is a form for scheduling of people in certain areas for week ranges across 2017, however I want to count the number of hours a certain individual is scheduled during a public holiday day.

for example in sheet "Annual Leave & DIL" is a list of all public holidays for 2017. What I want to count is if Name 1 is scheduled on any of those days across the other sheets then it reflects the hours worked that shift.

For this example on the sheet Week "15 - 18" date: 14/4/17 if Name 1 had either the code "PH" or 24 hour time in their row (Cells N11:O11) then in Cell B4 on sheet "Annual Leave & DIL" it would count the hours found in the cell P11 on Sheet "Week 15 - 18".

Any suggestions or advice or even if you had the time to put together the formula on the attached sheet that would be greatly appreciated!

Regards,
Marc

[JohnTopley]

Can a "name" have "PH" in more than one Sheet (so in "Annual Leave & DIL" there would be multiple entries for such a "name") ?

At this point, I think you might need VBA to achieve this.

[Mrmarc]

Yes this is correct so sheet "Week 15 - 18" could have multiple entries on a PH column, they could have PH entered in their row along multiple sections and they could also have other entries from other "Week" sheets to add to this.

VBA is something I haven't worked with so would need some solid guidance if that is possible..

[JohnTopley]

To me this a fairly complex "search/match" which requires identification of which sheet to search, match all the "HD & "DIL" entries and construct the "Annual Leave and DIL" table: the latter will (can) have multiple entries for each name.

For example "Name 1" has "PH"/"DIL" entries in "Week 15 - 18" but could have similar entries in any other period (?).

It would help if you could add some manual entries to the "Annual Leave & DIL" table, including multiple entries for "name 1" (as an example).

As per my previous most, I am sure this will require a VBA solution (and although I have some VBA knowledge) you may wish to consider closing this thread and re-posting on the the VBA/Macro thread.

Or wait to see if anyone on this forum offers a solution (formula/VBA).

[AliGW]

There is no need to close this thread! If it needs moving to the VBA section, I can do that - just ask here, please.

[Mrmarc]

This would be fantastic if this can be moved to the VBA section for someone with detailed VBA background could possibly help. Many thanks for your help so far team.

[JohnTopley]

@Ali: sorry .... was not sure of process for moving thread: in future I will refer to a moderator to do the necessary. Lesson learned!

[AliGW]

Thread moved and title changed to (hopefully!) properly represent the issue and attract the right helper.

@John - no need to apologise! It's the best way to avoid duplication of threads, I find.

[Mrmarc]

Thanks Ali for the shift and the advice/help so far.

[AliGW]

You're welcome.

[JohnTopley]

As per my post #4: it would be helpful if you could update your file with sample (expected results) data.

[Mrmarc]

Hi thanks for that, I have updated my sheet with comments in the sheet "Annual Leave & DIL" to show where I got these numbers from and what results I would like to show, however again open for suggestions.

Also keep in mind that I have only put the comments in for what is on the multiple sheets currently, there will be further inputs, codes and 24 hour times to be added as the year goes on so this will need to auto update as things are added etc.

and also the formulas would have to apply for all the names.