Excel _Attendance register

[Riaan1055]

Hi Guys 

Hope you are doing well.

I need assistance regards to a attendance register i'm working on.

I have 4 different shifts - A, B, C, D with 4 supervisors and the employees that work according to the specific shifts.

What I've done so far - I inserted a drop-down list with the different shifts that pulls in the supervisor per shift.

What I need - I need excel to pull in the employees under the specific shifts automatically into the register.

I've attached the excel sheet. 

Kind regards

Riaan

[#2]

@Riaan1055 Start by changing the shift names to Shift_A, Shift_B etc. so that they become the same as the named ranges you have given to the shift lists.

Empty the area where you now have the employees listed B5:D24 and insert enough empty rows to be able to contain your largest shift.

Provided you are on a fairly recent version of Excel, enter =INDIRECT(C3) in B5. It should pick-up the content of the entire named ranged based on the selected shift in C3.

Attached your file, including these changes for reference.

[#3]

Hi Riny

I tried your instructions and I still seem to not get it. Will you please have a look at my changes@Riny_van_Eekelen 

[#4]

@Riaan1055 Have a look again at the attached file. In case it doesn't work for you, what Excel version are you working with?

[#5]

Hi Riny

Im using office 365. If you check the document you send, when you change the shifts it onlys pulls 25 employees through. Are there specific reasons for that?

Thanks for the trouble

Riaan

@Riny_van_Eekelen 

[#6]

@Riaan1055 Added a few more empty rows. Seems to work just fine. See attached.

[#7]

Thanks @Riny_van_Eekelen for the assistance. 

Appreciate it very much.

Thanks

Kind Regards

Riaan

[#8]

Hi @Riny_van_Eekelen 

Hope You're doing we Will it be possible for you to assist me again with an attendance register I'm working on please.  As I have 4 different shifts, I'm getting the data of shift a when the drop down changes to shift b, c, d the data aren't pulling through.

You'll find the back end data on the lists sheet and the Register on TA van Wyk sheet. The drop down is in B1.

[#9]

@Riaan1055 

First of all, you merged cells B1 and C1. So, I couldn't select any option to begin with.

And then you added a lot of extra tables in "Lists". And you added formulae with the @sign. Why?

What seemed to be a simple question to begin with has now become rather unclear. What is it exactly that you are trying to achieve?

[#10]

I thought if I build a back end Attendance register exactly the same as the actual attendance that it will be possible to pull through all the info to the actual attendance register, that's why all the extra tables have been created.

I basically want it to have a drop-down in the first row: Attendance_Register_Shift_A, B, C, D and when it changes the employee info pulls through with the machine info included.

I tried to work on your previous info but only seem to get Shift A's info and not the the rest.

What do you suggest? @Riny_van_Eekelen 

[#11]

@Riaan1055 Am not sure that this makes any sense. How do you intend to update what you call the "back end registers"? Why then not create one separate sheet for every shift?

If I would want to design a more dynamic approach, I would start by creating a set of tables that tie Employees, Machines and Shift calendar together. Then the Attendance sheet would set the Shift and Time period for which you want the overview, and then all other information should get pulled in automatically from the other tables. Haven't really thought through it and it will certainly be more work that just helping with some Excel formulae.