Dear All
i would like to copy data for only name present in column J that represent for column I
the data
A23:g35 copy from A7:A20 but remove the absent from this in represented date
Dear All
i would like to copy data for only name present in column J that represent for column I
the data
A23:g35 copy from A7:A20 but remove the absent from this in represented date
Last edited by mazan2010; 07-04-2017 at 11:35 AM.
in A24
=IFERROR(INDEX($A$8:$G$20,SMALL(IF(($J$8:$J$20="Present"),ROW($A$1:$A$13)),ROW(A1)),COLUMN()),"")
Array formula, use Ctrl-Shift-Enter
copy down and across to G35
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Dear Special-K
its working good if look only one date but if the date change for example in my new file it's not working
i need look to absent and present in the represent date
in A24
=IFERROR(INDEX(A$8:A$20,SMALL(IF(($J$8:$J$20="Present")*($D$8:$D$20=$J$7),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")
...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.
copy across and down
Date check added
Last edited by JohnTopley; 07-04-2017 at 12:36 PM.
its not working please my attached file
thanks for your attention and reply
See post #4
Dear JohnTopley
its not working if i change the date
can see my attached file
i found this post in other site but can't modify it to fit my needs
https://stackoverflow.com/questions/...-results-sheet
If you use the right date reference .....
=IFERROR(INDEX(A$8:A$20,SMALL(IF(($J$8:$J$20="Present")*($D$8:$D$20=$B$2),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")
OR
=IFERROR(INDEX(A$8:A$20,SMALL(IF(($J$8:$J$20="Present")*($D$8:$D$20=$K$7),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")
dear JohnToply
i put last your formula but still not working
i think you didn't understand me exactly
i have employ at work , using attending (absent or present ) that generate data for employ in case present only
this attending data in column I:M column
hope now you what i need is clear for you
Did you enter it as an array formula ??????
And you need to change the reference form J to K.
I'll look at INDEX replacement for this.
Last edited by JohnTopley; 07-04-2017 at 02:28 PM.
Try
=IFERROR(INDEX(A$8:A$20,SMALL(IF((INDEX($J$8:$Z$20,,MATCH($B$2,$J$7:$Z$7,0))="Present")*($D$8:$D$20=$B$2),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")
...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.
Formula corrected ....
Last edited by JohnTopley; 07-04-2017 at 02:35 PM.
thanks it working very well now
many thanks Dear JohnTolpey
really you all the time help me in my post
Corrected formula in #15
yes its work good
I changed K7 to B2 reference in last formula: make sure your formula has this change.
=IFERROR(INDEX(A$8:A$20,SMALL(IF((INDEX($J$8:$Z$20,,MATCH($B$2,$J$7:$Z$7,0))="Present")*($D$8:$D$20=$B$2),ROW($A$8:$A$20)-ROW($A$8)+1,""),ROWS($A$8:A8))),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks