Looking up values using multiple conditions

[xtr3m3dud3]

Hello everyone, I am trying to compile an audit sheet in which I am collating individual spreadsheets from different auditors in a pre-defined format. The "Main" tab in my attachment shows how I want the final output to look like, and the "Audit Sheet" tab contains the data that will be provided to me by my auditors. There will be multiple Audit Sheets for different auditors (date wise) that needs to go in the respective columns for each auditor for each audit types with their scores.

Hope I am able to explain this clearly, if not then please let me know. Thank you.

https://app.box.com/s/qm9474crjlpkq2ben6kp

 

[yesterdays]

Okay :
=INDEX(INDIRECT("'"&$B4&"'"&"!$B$15:$K$17"),RIGHT(C$2,1)*1,$A4+ROUNDUP((COLUMN()-2)/3,0)-1)

This formula will pull data from the sheet which name match the agent cell ( i.e Main sheet B4: "Smith")

 

[xtr3m3dud3]

Please correct me if my understanding is wrong, but I don't see any reference to the tab "Audit Sheet Smith".....what am I missing here.....In a real time situation, I will have 10 tabs (1 for each auditor), from which I will be transposing only the Case #s and the Auditor's Name as shown in my file from these 10 tabs in the "Main Sheet".....now I want that the moment I transpose the Case # and Auditor's Name, the score should populate as per the scores provided in each tab with reference to the case#, auditor's name and the audit scores....

 

[yesterdays]

I never know exactly what is your audit sheet name, it is "Audit Sheet Smith","Audit Sheet Donna"..... isn't it ?
well try this again
=INDEX(INDIRECT("'Audit Sheet "&$B4&"'"&"!$B$15:$K$17"),RIGHT(C$2,1)*1,$A4+ROUNDUP((COLUMN()-2)/3,0)-1)