Absence tracker problem

**pandabear28** · 12-11-2014, 09:49 AM

Hi
I am trying to pick data from one table and dump it another and my head is going.
In the attached file you can see what I am trying to do (hopefully)
I have a holiday sheet but i need to be able to pull the data from the sheet by entering a week no in the recieving sheet(Tracker) and pulling only the data for that week and only for people who are absent.
As you can see in the attached file I have entered week 1 in the tracker and I need to pull what the tracker shows . You can see that bill is not in the tracker because he has not been absent during the week. I have seen something about using a Vlookup whith a "Match" inserted in the lookup formula this sort of looks the way to go but I am still having trouble in getting it to only select the data from the week no I enter in the tracker.
Any help would be great.
PB

**ChemistB** · 12-11-2014, 10:04 AM

Since you are varying both rows and columns, try using INDEX/MATCH
In M20 copied across and down

=IF(LEN(INDEX($D$3:$R$8, MATCH($J20,$A$3:$A$8,0),MATCH($L$16, $D$1:$R$1,0)+COLUMNS($A$1:A$1)-1))>0,1,"")
Does that work for you?

**pandabear28** · 12-12-2014, 04:43 AM

Hi
First thanks Chemistb for your reply.

That works great for inputting the results in the table for the people in the tracker. But

the employee names are not constants I need them to be imported into the recieving table only if they are absent for any day for the specified week.
If I enter week 1 bill has no entries (he is in work all week) so he should not be in the recieving table (TRACKER) same for week 2.
if I enter week 2, Bill, Tom & Cath have no entries so they should not be in the tracker.
However
If I enter week 3 bill has an "off" day on Wed so his name should then enter the tracker but Tom, Sam & Sue have no entries so they should not.

Hope this makes sense.
I only want employees in the tracker if they are off during the week specified.
I think I may have to do this in two steps ?

**ELeGault** · 12-12-2014, 05:23 AM

Create a Helper column in front of the imported sheet, so you will always paste that data starting in B...

Then write the following

Label A2 either Helper or VlookupValue

A3 would be

=IF($D3=WEEKNUMSelected,CountA($A$2:$A2)-Countifs($A$2:$A2,""),"")

EDIT : IF needed add to the above, IF something = OFF then be Blank or anything like that before you do the count to prevent pulling them in...just an option if needed...
Week Num Selected would be what ever you put in the sheet...

Now the table to pull them in on, you can list down 1 - 50 (If you need that many) and perform a Vlookup on the import sheet

For the Name

=VLookup(REF1,ImportSheet!$A:$C,2,False)

For the Fin No.

=VLookup(REF1,ImportSheet!$A:$C,3,False)

Ref1 would be the 1 - 50 numbers, then you can try the rest with the previous solution now that you have the names pulled dynamically for that week!

Absence tracker problem

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Absence tracker problem

Re: Absence tracker problem

Re: Absence tracker problem

Re: Absence tracker problem

Thread Information

Users Browsing this Thread

Similar Threads

Tracker tool problem

System idle time tracker / Break Tracker

Big number of Staff - problem of replacement / absence

Employee Tracker - leave of absence including earned leaves and used leaves and remaining

Absence Tracker will not calculate

Bookmarks

Bookmarks

Posting Permissions