Hi Andy,

Firstly, welcome to this great forum, you will find great help and knowledge here.

I had to rearrange your tables on each sheet as per the January tab, it was the only way I was able to get this to work as you wanted.

Please review my attachment and see if this is the result you are looking for.

Good luck with your project.

Hi Andy

As James said above, it is best if you can rearrange your data properly. Otherwise the formulas can get very messy (as per attachment).

Good luck.

Sunny

Hi Sunny/James, thanks for your help. James, I tried your formula on my actual document & it worked great.

I have a further area of development/automation but I'm not sure if it is possible. I'm looking for employee names to be instantly populated in each monthly worksheet from a central worksheet as new one's start & other's leave. I have an admin who will be completing this and I want to try make it as easy and useable as possible. I guess at a basic level it would be a staff database with the start month and a leave month, therefore if they leave in October they are not included in November's onwards. The number of employees I have is not going to exceed 20, therefore it is possible to keep all the active employees names in the same 20 cells in each monthly worksheet.

If it is possible I'd really appreciate it if you could provide a sample worksheet on this.

Thanks

Andy

Andy,

I am glad the formula worked for you and gave the results you are looking for.

As for your question on the staff database, I have not come up with a working solution as yet, but looking into a solution ... but no luck as yet!

I am guessing a index and match with if formula thrown in .....

Anyone else have a solution and possible angle...??

Thanks

Hi James/anyone, any thoughts on previous post? Is it possible?

Andy

Hi Andy

Give this a try. Haven't 100% tested though. Please do not touch row 1 of the Data sheet but you can hide it.

You will then need to use the formulas given by James to consolidate the result.

Hope this helps.

Sunny

Hi Sunny, thanks for doing that, looks pretty impressive to me. I think it has to ability to work, the data tab definitely works. Only thing is, when I remove the { } at the start/end of the formula's in the month tabs or copy the formula to my worksheet (which has to remove the { } signs) it goes blank.... not sure why & the formula doesn't work. Any ideas why that happens?

Hi Andy

Those {} belongs to array formulas. You don't key them in.

When you enter or edit such formulas, you need to press CTRL+SHIFT+ENTER instead of ENTER.

The {} will be automatically inserted.

You can read more about array formulas here  https://www.myonlinetraininghub.com/excel-array-formula

Sunny

Hi Andy

In addition to the link above, you can also refer to this :  https://www.myonlinetraininghub.com/excel-remove-blank-cells-from-a-range

This link contains the original code that I modified to remove blanks from the name list.

My codes can still be shorten by removing the part +ISERROR(Table14[JAN]), +ISERROR(Table14[FEB]) etc as I was using it to remove both blank and error cells.

Sunny

Hi guys, I received some previous help on the development of a spreadsheet (see above). All works great.

On Sunny's 'Andy2' workbook I'm looking to extend the same principles on employee names instantly being populated in each monthly worksheet to a quarterly version. So if an employee is with us for Q1 (Jan - March), they automatically populate in an equivalent Q1 worksheet...& so on.

Any help would be appreciated.

Thanks

Andy

Hi Andy

Give this a try. I have use some helper columns to get the data and then referred to them from the Quarterly sheets.

Hope this helps.

Sunny

Thanks Sunny, works perfectly. 

Last thing I need to analyse/collect is number of sick days across the year in an 'annual' tab. I think it's more complex because the names of the staff will potentially differ in order, in each quarterly tab, depending on when they start/leave. Is this possible to collect? Kind of vital really so I have an annual overview.

Thanks

Andy

Hi Andy

You can try this. It is a bit more complex as I don't know how many unique staff are there in a year. I have catered for 40. You can amend it if necessary.

Hope this helps.

Sunny

Hi Sunny, thank you for your expertise on this. The sickness tracker is working really well & I've learned a lot from it also.

I'm trying to add an automatic calculation for staff turnover during the year but I'm not 100% how to do it. The premise would be something along the lines of:

IF > the employee has a 'leave date' inputted, then add up all the contracted hours for all employees who leave in a year & divide by a given number, say 500 (I can put a formula in to work this out this number)

There isn't a column from contract hours in 'Andy 4' so just put a notional 36hrs for all of them.

Thanks

Andy