Return Date Value

[evansbryand]

I am brand new to this forum site so I hope I am posting this in the right spot. Please help if not . I have an excel workbook that holds the attendance records for a youth council I manage. I want to search this worksheet for when a student was absent, between a date range and return in another worksheet, the dates that they were absent depending on what name I put in the student name box on that worksheet. I have intermediate skill with excel, I just need a little guidance as to whether there is a formula that does this or if there is a macro that I can use. FYI- I am a complete novice when it come to Macros. Please Help! Example.xlsx

[Pichingualas]

Attachment 149548

I hope that helps.

[rylo]

Hi

Select the data sheet, then Formulas, Name Manager, New, name: datarng, refers to: =OFFSET(Data!$A$1,MATCH('Return Value'!$B$1,Data!$A$2:$A$6,0),1,1,5)

Select the Return Value Sheet
B2: =IF(ROW()-1>COUNTA(datarng),"",INDEX(Data!$A$1:$F$1,1,SMALL(IF(datarng="A",COLUMN(Data!$B$1:$F$1),""),ROW()-1))) This formula is array entered. Hold down the shift and ctrl keys, then press enter.

Copy from B2 down to B6

You will have to correct the name on Data from Student2 to Student 2

HTH

rylo

[evansbryand]

Hi,

Thanks for that response rylo. It worked perfectly but the actual workbook I have looks different and is named differently. I am having trouble renaming the formula to work for the actual worksheets I have. I have uploaded one identical to the one I am using. I would like to search the (Attendance Database worksheet) by the ID# and return the days absent in the (Attendance Warning Letter worksheet) if the Date is between the date range specified in the (Current Status Alerts worksheet). I so appreciate your help. This is making work so much easier. Examplesheet.xlsx

Best Regard,
B

[rylo]

Hi

OK, I've updated the named range, and put some formulas into Warning Letter C14:H14 and C22:H22. Hopefully that will get you going.

I've also updated your vlookup formula on Warning Letter to cover off the extra comma you have added to the name, so it should bring back the right violation date.

823974.zip

rylo

[evansbryand]

Rylo,
This worked perfectly. Thanks so much for your help.

evansbryand