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]

What is the "XX" in Audit Sheet mean? Is it "Smith", "Donna"...?

 

[xtr3m3dud3]

Hi....its just the number that will be populated....xx scores sort off

 

[yesterdays]

So it's will be like this:
Main sheet :
C4 ='Audit Sheet'!B15
D4 ='Audit Sheet'!B16
...
The XX had nothing to do with the Main sheet right?

 

[yesterdays]

=INDEX('Audit Sheet'!$B$15:$K$17,RIGHT(C$2,1)*1,$A4+ROUNDUP((COLUMN()-2)/3,0)-1)

paste this in Main sheet , C4
The rest just fill and drag
Hope this help

 

[xtr3m3dud3]

So it's will be like this:
Main sheet :
C4 ='Audit Sheet'!B15
D4 ='Audit Sheet'!B16
...
The XX had nothing to do with the Main sheet right?

I have updated the sheet with my comments.....please re-open the dropbox to view the updated spreadsheet.

 

[yesterdays]

So the name of audit sheet : "

James 14112014​

"

What if their is another sheet " James 16112014"?
Then what sheet you want to pull data from?
I think you have to have another column indicate date​

 

[yesterdays]

And one more thing, your data in your main sheet seem wrong to what you need
should it be like this :

		Audit 1	Audit 2
		Score 1	Score 2	Score 3	Score 1	Score 2	Score 3
Case #	Agent
1	Smith	5	5	3	4	2	5
2	Donna	4	2	5	2	3	5
3	Jewel	2	3	5	4	4	4
4	Fabian	4	4	4	4	2	1
5	Renee	4	2	1	5	5	3

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[xtr3m3dud3]

Date column is not needed as the compiled filed will be maintained for each date.

I have now only kept 1 tab for better clarity....hope this helps....

https://app.box.com/s/0abfyfmxl54mhmnqn0z0

thanks for your time

 

[Peter_SSs]

Just a comment that you are excluding many potential helpers by providing both your sample data and, apparently, some you your requirements in on off-forum site/file. There are many experienced helpers here who do not wish to (or are prevented from by workplace security settings) download such files and will therefore bypass your thread. My signature block below has suggestions for posting small (copyable) screen shots directly in your post.