Retrieve Information based on Condition

[aposatsk]

My chart is as follows:

Name------------Company---------Date-----------Score--------Pass/Fail
????-------------?????????--------??/??/????--------??-----------????
**
(** this says Pass if the score is 10, fail if it is between 0 and 10,
"Not Evaluated" if the score is 0, and "Invalid" if the score is above
10)
and the list continues with many names.

I want to make two seperate charts that retrieve information from the
above chart.

The first one will retrieve only the names of the people who have done
the test but the tests have *not* been evaluated. I would like it in
the same type of format, with name, company, date, etc. This will allow
us to track down the people who have not been marked but have done the
tests.

The second chart will pull out people who have failed the test (the
mark is between 0 and 10). Once again, all the information should be
there.

Thank you!

 

[mr_teacher]

I have had a very similar issue with a spreadsheet of mine. I haven't
come up with a great solution but I have found a workaround - not very
pretty and is a bit of a pain but does work!

On the second sheet in cell E2 you could use the formula
=if('Sheet1'!E2="Not Evaluated",'Sheet1'!E2,"zzz")
Then in D2 use the formula
=if(e2="zzz","zzz",'Sheet1'!D2)
Then in C2 use the formula
=if(e2="zzz","zzz",'Sheet1'!C2)
In B2
=if(e2="zzz","zzz",'Sheet1'!B2)
and in A2
=if(e2="zzz","zzz",'Sheet1'!A2)

Copy these down throughout the spreadsheet

Then I have used conditional formatting to change all text that is
"zzz" to white.

Finally I have created a macro that runs when the worksheet opens that
sorts everything into alphabetical order based upon column E - so all
your 'Not Evaluated' cells will then show at the top and it will then
appear as though the rest are not there and you only have these
showing.

You can then apply the same ideas to your other worksheets - just
changing the words "Not Evaluated" for "Fail".

As I say I am sure this is not the most elegant way of doing things -
and it might not be the ideal solution for you? Hopefully it might give
you some ideas of how to go about it - and if you think / find any
better ways round of doing this let me know too and I might be able to
improve mine!!

If this doesn't make any sense you could always email me a sample of
your spreadsheet if you like and I can show you what I have done that
way.

Regards

Carl

 

[aposatsk]

Does anyone know of a simpler way?

Thank you.