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.