• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Attendance register

G

GingerNinja

New Member
Ok, so as the title, i'm tring to make an interactive attendance register. I've pretty much taken the idea from Chandoo's awesome pivot calendar_v1 with the date table on the 1 page (dates going down in 1 column, exactly like the pivot calendar) and a splicer calendar on the next (linked with a pivot from the date table, exactly like the pivot calendar)

On the 3rd page i have the attendance register that will be filled in daily. I have employee names going down one column with the related dates of the month going accross 1 row, but separate tables for each month( which i suppose i could just make 1 loooong horizontal table). Which then gets filled out if the person is on day shift, night shift etc...

Now because im wanting that splicer calendar to reflect this attendance ie: Day, Night etc, but by person per month, i need to add the attendance onto the page 1 table of dates to add it to the pivot data the splicer is working from.

Now we get to my dillemma....How to create the vertical list from the horizontal table from the attendance.

I'm thinking i need to use the INDEX function with V/HLOOKUP functions nested in to determine the row/column numbers INDEX needs. But i dunno how to do it...yet. Also, because i'm lazy i'd like a function i could type into the first cell and drag it down. I'm thinking i might need helper cells to get to the row/column numbers, this won't be an issue.

Otherwise, there is an option i can think of, but i'd need to add in numbers on the attendance tables.

Oh and the name that will change on the column is linked to a combo box near the splicer calendar.

I hope i wasn't vague in any area's. I can elaborate more if needed.
 
Hi ,


Since you are trying to describe the structure of your worksheets / data , it would be easier on everyone if you could upload your file , without any data , but with the tabs , headers ,...


Is this possible ?


Narayan
 
I would gladly... lolz, but i dunno how to add an attachment here...

But ya, it's still in dev phase, so there is no names and such. I was hoping to have it implemented around my factory too so i'm keeping it generic.

It is macro enabled, but just the RefreshAll macro when you change the spinners.
 
Lolz, figured it out...

Here, hope it works. It's my first time...

https://rapidshare.com/files/901201252/Attendance%20CalendarV1.xltm
 
Hi ,


This forum does not have any provision for attaching files to posts ; what you can do is , upload your file to any file-sharing website e.g. Rapidshare , Hotfile , DropBox , Skydrive , Google Docs , Speedy Share ,... , give everyone access to download your file , and then post that access link in this same topic.


I would prefer that you upload to any website that does not require one to register before downloading.


Narayan
 
I suppose i should add that the data in the attendance page is just taken from a previous register i kept. So it's fine if you mess around with it a bit. :)
 
This is similar to what i'm looking for.

https://rapidshare.com/files/1399826838/Attendance%20CalendarV111.xlsm

But the combo box will be on the Employee page instead of the date table page.

You will notice how i rearranged the whole table in the attendance page to be "horizontal". I don't really like this layout as printing would be an issue, but not he end of the world.

If anyone can help with the original layout, it would be much appreciated!!!
 
Hi ,


I downloaded your initial file ( Attendance CalendarV1.xltm ) , and I am no nearer to understanding exactly what you want.


You have the following sheet tabs in your file :


1. Date table

2. Attendance input

3. Employee

4. Pivots


Can you specify exactly which sheet needs to be worked on , which cells need to be calculated , and what data will have to be used to arrive at these cell values ? If you can work out a couple of calculations with data from your file , it may make it easier for others to know your requirements.


Also , if you are looking for solutions which will work using only Excel 2010 , please mention this.


Narayan
 
Mujhe maafkarodho.

I thought if i spoke about splicers this would give the idea i am working with Excel 2010 and as it is for my work, i wouldn't be looking for solutions that would be backward compatible with 2007. I get a bit enthusiastic when working on bigish workbooks like this and can get vague. I sometimes find it hard to explain what i am seeing in my head...

Ok, Date table!H5 is controlled/changed via the combo box on Employee page. Now when this changes, the column H will update and show the values from rows 5 downwards on Attendance input page relating to the corresponding dates. For example, Date table!H6 will display what is in Attendance input!C6 and Date table!H7 will display Attendance input!D6 etc.. if Employee 1 is selected from the combo box.

Hope this is better. The rest i will work on as it comes up.
 
You must log in or register to reply here.
Back
Top