So basically what i am looking to do is be able to select one of the day from the drop down menu in the daily attendance sheet and have it auto mantically populate from the students list if the have a yes next to their name for that day. I have included my desired result to give you an idea of what I'm looking for. Any all help would be greatly appreciated.
TIA
LOS
Hi
Use this formula in C4 and copy down
=INDEX(OFFSET(Students!$A$4:$A$89,0,MATCH('Daily Attendance'!$B$1,Students!$B$1:$J$1,0)),MATCH('Daily Attendance'!A4,Students!$A$4:$A$89,0),1)
Formula:
Please Login or Register to view this content.
it is giving me a circular reference error.....
In C1 =IF(B1="Monday",2, IF(B1="Tuesday",4,IF(B1="Wednesday",6,IF(B1="Thursday",8,10))))
In c4 =VLOOKUP(A4,Students!$A$4:$J$89,INDIRECT("C1"),FALSE) and copy down
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Not for me. See the file Daily Attendance.xlsxOriginally Posted by Losguapos1
Maybe i wasn't clear with what i was looking to get out of the data. i need a sheet that will have the students name (Student 1, Student 2 etc) listed if they have a yes next to thier name. I want to be able to select the dat form the drop down list and have it give me all of the students who are enrolled on the at day.
Thread #4 worked for me on your worksheet, and Jose's also worked for me.
Rereading your post. Do you not want to see the NO's. Only the Yeses. If a student is a No on a particular day, his name should not appear on the list. Is this what you are looking for? If yes, then I believe we will need to employ a VBA solution.
Last edited by alansidman; 11-02-2015 at 07:23 PM.
yes i am looking for it to return only the students names who have a yes next to them.
CSE formula
Formula:Please Login or Register to view this content.
using combination of offset/if/small/index
see attached result
columns E to L are there just to cross check
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
actually second offset not required
this should be suffice
Formula:Please Login or Register to view this content.
Here is a VBA solution. this will require that you remove merged cells (unmerge them) in the first sheet.
..
Please Login or Register to view this content.
How to install your new code
- Copy the Excel VBA code
- Select the workbook in which you want to store the Excel VBA code
- Press Alt+F11 to open the Visual Basic Editor
- Choose Insert > Module
- Edit > Paste the macro into the module that appeared
- Close the VBEditor
- Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)
To run the Excel VBA code:
- Press Alt-F8 to open the macro list
- Select a macro in the list
- Click the Run button
alan
i think your select case is shifted off
shouldnt it be 2/4/6/8/10?
also i think your first SLR should be
otherwise you delete the students headerPlease Login or Register to view this content.
**(if the data has been wiped already)
combined alan's solution with worksheet_change event based on B1
Thanks Hum
Senior moments prevail.
Alan
This uses a helper column in Daily Attendance. It can of course be relocated or hidden. The formula for that isThen in column BFormula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Dave
Copy this in A4:
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.Please Login or Register to view this content.
Drag down.
Quang PT
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks