What is the formula to place an X in a box and have that name transferred to another worksheet in Excel?

Question

I have a workbook with many tabs. I want to enter people's names on one page and then be able to place an "X" in the columns under the classes they wish to enter. I want that person's name to then be copied to the corresponding tab (worksheet) so I don't have to enter their name over and over. Can you help?

Answer 1

let say you have these worksheets:

Enrollment,Science,Math,English

(i use semicolon delimited data, hopefully you understand this format)

in Enrollment sheet you have something like this:

;Science;Math;English

Marcus;;X;X

Michelle;X;X;

Samantha;X;;X

Simon;;X;

in each of class sheet, i use this trick, using two formulas, in column A and B:

=IF(Enrollment!B2="X", Enrollment!A2, "" )

and

=IF(Enrollment!B2="X", 1, 0)

replace Enrollment!B2 with corresponding class address... In this sample, B2 for Science, C2 for Math, D2 for English

drag mouse to copy both formula down, rows == sum of students

focus on any row in B column that have the formula i written above (columns that's filled with 1 or 0).. and do sorting from largest to smallest

now you can hide column B