Pulling information from one sheet to another dependant on the value of specific cells

Hi guys,

This is my first post on here, although i have used this site before when i have had quiries.
But today unfortunately i cannot find what I'm looking for. So hopefully someone here can help

I have attached a copy of a simple spreadsheet i am working on. Its not the prettiest thing in the world but for now it will do what i need it to.

I am just struggling on the summary sheet. This is maybe where it could get complicated.

As you will see there is a table with "name", "payroll"... etc.

So heres what i need:

Dependant upon the date i need the table to show issues from the "record" sheet. So if i put a date into cell B1 on the "Summary" sheet it will pull across the relevant information from "record". As you will see on "record" there is a drop down list and dependant on the chosen value it should trigger the summary sheet.

For example:
If the date is 1/1/13, i put this into cell b1 on the summary sheet, i want the summary sheet to firstly find the correct date in row1 of the record sheet and then pull all the people with the dropdown value set as ABS.

I just want it to pull the names. I know how to use the vlookup for the rest. It just the initial part of it finding the date i set and pulling the relevant names across.

Is this possible??

Thanks,

Hi. The array formula below populates names which are selected ABS in particular date. Sorry that formula is long but i am sure here are excel gurus which gan present you a lot shorter alternative

Summary A4 = =IFERROR(INDEX(Record!$A$3:$A$21,MATCH(SMALL(IF((INDEX(Record!$M$3:$PK$21,0,MATCH(Summary!$B$1,Record!$M$1:$PK$1,0))="ABS")*ROW(Record!$A$3:$A$21)=0,"",(INDEX(Record!$M$3:$PK$21,0,MATCH(Summary!$B$1,Record!$M$1:$PK$1,0))="ABS")*ROW(Record!$A$3:$A$21)),ROW(Summary!A1)),IF((INDEX(Record!$M$3:$PK$21,0,MATCH(Summary!$B$1,Record!$M$1:$PK$1,0))="ABS")*ROW(Record!$A$3:$A$21)=0,"",(INDEX(Record!$M$3:$PK$21,0,MATCH(Summary!$B$1,Record!$M$1:$PK$1,0))="ABS")*ROW(Record!$A$3:$A$21)),0)),"")
then drop down

Thanks for the quick reply and it works great. +REP

But hopefully someone can provide a shorter more manageable formula.
Something i can learn to use and manage myself.

Thanks

Looking into this more, I think a bit more information from myself is needed so that you guys know what it is I’m asking and can then hopefully provide the relevant information or tell me if it’s even possible.

What I really need to know is:

VVV This formula will be put into cells B4 (Summary sheet) all the way down VVV

1.How FIND the date I input to cell B1 (Summary sheet) in cells M1:PN1 (Record sheet)

2.How to pull the relevant PAYROLLS from the Record sheet for people with ABS,A2,A3,Sick,SS,SN,LTS,LVR. NOTE - There may/will be multiple payrolls to pull across in to the table on the Summary sheet.

3. Is there anyway to "sort" these so that all ABS,A2,A3(people absent from work) appear in the table first then Sick,SS,SN(people who are sick) then LTS(people who have given long term sick notes) then LVR(people who leave)?

VVV This formula will go into cells C4 (Summary sheet) all the way down VVV

1.How to pull the reason that triggered the payroll being pulled across. If for example I put the date 6/1/12 into cell B1 and on that date A Smith is ABS I need that to show in the issue column. This also applies for A2,A3 etc. what ever the trigger is.
VVV This formula will go in to cells E4 (Summary Sheet) all the way down VVV
1. Is there a way to pull the value that is in the relevant column to what ever the trigger is. So if it is A Smith and he is ABS then I need it to pull the cell value of F4.
SO:
ABS,A2,A3 should pull cell values from the F column (Record sheet)
Sick,SS,SN should pull cell values from the E column (Record sheet)
LTS should return 0
LVR should return 0
And finally!!! (I think…)
VVV Formulas for the Summary table and issue count (Summary Sheet)VVV
1. Dependant on the date I put into cell B1 I need it to pull the relevant information from the “Daily” Sheet.
So:
Expected (H4 Summary sheet) – pulls the information from row 5 (column depends on the date)
Delivered (H5 Summary sheet) – Pulls the information from row 6 (column depends on the date)
And so on for Percentage, Absent, Sick, SN,Uhol,LTS.

I hope I’m not asking to much here guys, please tell me if I am. I would appreciate any feed back possible even if it just covering one part of it… If you can help or point me in the right direction I wont hesitate to give you your deservered REP.

Thanks

PS. Ive attached a new sheet with individual payrolls so you can play about and test it.