Match entries with table names

[nikhil83]

Master table Schema - Index[Course,No of Nominations,No Attended,No of evaluation completions] : Its a summery table to capture numbers corresponding to course details.
Sub table Schema - Course name[Names of Nominated empl,Aattended/Not,Evaluation score].A sub table with same name as course name will be created for each entry in Index[Course] to capture course details.

Suppose I need to get Number of nominations for a course named MVC, in other words , i need to fill Index[No of Nominations] for the row Index[Course]=MVC . Then i need to search for table with Name MVC , Then count no of entries in MVC[Names of Nominated empl] and return that number to Index[No of Nominations] for the row Index[Course]=MVC .

PFA sheet that depicts what i mentioned.Can you let me know the formula to get Number of nominations for a course please....

 

[Bob Phillips]

Try

=COUNTA(INDIRECT(Index[@Course]&"[Names of Nominated empl]"))

=COUNTIF(INDIRECT(Index[@Course]&"[Attended/Not]"),"Y")

=COUNTA(INDIRECT(Index[@Course]&"[Evaluation Scores"))

 

[sambit]

Hi,
plz find attachment. sheet having name "method1" and "method2".