Validation Of Names Required from given List :confused

[pipsmultan]

Greetings to all

I am running into big problem :banghead:

i tried many alternatives but none of them are helping :(

I have two sheets called “Timetable” & “Teachers Name List”

What I want is any Possible Best Solution which can check and validates names of Teachers into my “Timetable” sheet from “Teachers Name List” sheet

So that if any one enter wrong spelling of any teachers in “Timetable” sheet, it Prevent from entering it and give error message “Spelling mismatch from Teachers Name List sheet”

Excel file & Image are attached

12009

12007

And good Help from any one will be highly appreciated

Many thanks

[mancubus]

hi.

what you need is data validation.

below is a good tutorial with files and videos.

http://www.contextures.com/tiptech.html#Go_D

and before the tutorial, you shuld consider redesigning your spreadsheet. start redesign with avoiding merged cells.

[pipsmultan]

I know it is only possible if I can afford not to have merged cell and can enter names in cells.

but my big problem us I can not afford to have merged :(

any other alternate will be highly appreciated

Many Thanks

[mancubus]

there are always alternatives to complete a task.


the table(?) in timetable worksheet has column headers in row 4 and row headers in column A.

from database point of view, a record (row) contains values from fields (columns).

the cells in timetable contain 2 or 3 field values: Lesson, Teacher, Class.

and you want validation for Teacher field only.


for me, your time table can be called a report but not a table. if you want to keep it, ok.

if i were assigned a task like this, i would add another worksheet, name it daily_program, design a table in cell A1 whose each cell contains one bit of info, and use the validation here.


a sample design:

Column Headers in A1-J1
Date | Period | Time | Class1 | Class2 | Teacher | Lesson | Sex | Header9 | Header10

first record in A2-J2
24/07/2014 | 1st Period | 07:00 to 07:35 | 10th | (East) | Sir William | Math | Both | C.ing | SampleText

im my design columns B thru I are all validation cells. you can create lists of unique values from these columns as validation lists.

for example, if i read your table correctly boys and girls can have some lessons separately. so i would add a validation to cells in column H like "Both, Girls, Boys""

after entering all the records in this new worksheet, prepare your report in Timetable via pivot table, formulas or macro.


thats what i can recommend at most.


cheers.

[pipsmultan]

Your Validation Idea via pivot table Sound Good...

i m sending the link of my Excel file



12019

can you please update in my excel file

Regards

[mancubus]

i heard nothing about "data validation via pivot table", let alone recommend it.

for the file you posted, i can't be of any help to you. maybe another member here can help.

if you want me help you any further, you should upload a file that is designed in a way which i described in post#4.