I'm making a spreadsheet to track whether course prerequisites are being met by the recommended course sequence at the university where I work. What I am wondering is whether students can meet all their prerequisites if they take their courseload in the order we recommend. On my sheet, course numbers are listed in the order they must be taken down column C. Prerequisites for each of these courses are in column H. My nested IF statement currently looks like this:

=IF(OR($C$5 = H18, $C$8 = H18, $C$9=H18, $C$10=H18, $C$11=H18,$C$12=H18,$C$13=H18,$C$14=H18,$C$15=H18), "yes", "NO")

The idea is that the function will check each of the C cells for the prerequisite value in H. If the prereq. has been met, a "yes" will appear. If not, then a "no," and I will know to adjust the recommended course sequence accordingly.

The way it is now technically works. But is is possible to replace all the individual C cells I'm listing with a range, something like $C$5:$C$15? Because I have to maintain this sheet, it would be much easier to update it each semester if I could use ranges instead of having to type out each cell I'd like checked.

Is this possible??

You could try a =COUNTIF() function: =COUNTIF(C5:C15,"H18")

If the string H18 appears within the rage c5:c15 the =COUNTIF() will return the the number of times it does.

if H18 appears once, you get 1

if H18 appears twice, you get 2

if H18 does not appear, you get 0MIKE

If you want to stick with your "YES" or "NO" then this should work: =IF(COUNTIF(C5:C15,"H18"),"Yes","No")

Also the string "H18" can be replaced by a cell reference.

If the string H18 is entered into cell D1 then=IF(COUNTIF(C5:C15,D1),"Yes","No")

will also work.

This allows you to make a list and just reference the appropriate cell in the list, as opposed to entering the string by hand all the time.

MIKE

That is fantastically easier, and so helpful! Thank you!

Ask Your Question

Weekly Poll

Do you agree with Google's decision to shut down Google+?

Discuss in The Lounge

Poll History