Hi Max,

I copied and pasted your formulae into cells A2, B2 and C2 of my new sheet.
I then copied the respective formulae down columns A, B and C.

Columns A and C are blank. In column B, it returns "#N/A".

Not sure if this could be a problem but in my "Subjects" sheet, I select the
"Yes" or "No" options using a dropdown list.

Thank you for your patience.

"Max" wrote:

Prem: Should have worked exactly as I advertised. Re-check carefully the 3
top line formulas provided that you entered into A2:C2. Ensure that these
correspond exactly with what I responsed, especially this term: ROWS($1:1).
Excel has a nasty habit of tending to remove the "$" sign in ROWS($1:1) if
you rush through its prompts when you enter the formulas. Try it again, post
back here. Zonk the YES below to celebrate success.
--
Max
Singapore
---
"prem" wrote:
Hey Max,
Tried your method, but all I get is a bunch of zeros in column B. Not sure
what the problem is.

Again, in the "Subjects" sheet, the students' names are listed in column A,
staring from A2, then A3, A4 and so on.

In Column B, it will list if the students are taking the subject Chemistry
or not. So in B2, it will say either "yes" or "no" to indicate if the student
listed in A2 is taking Chemistry. Similarly, in B3 it will indicate if the
student in A3 is taking Chemistry or not.