Looking up values using multiple conditions

xtr3m3dud3

New Member
Joined
Jul 8, 2013
Messages
26
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

yesterdays

Active Member
Joined
Oct 8, 2014
Messages
337
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

Active Member
Joined
Oct 8, 2014
Messages
337

ADVERTISEMENT

Code:
=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

New Member
Joined
Jul 8, 2013
Messages
26
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

Active Member
Joined
Oct 8, 2014
Messages
337

ADVERTISEMENT

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​
 
Last edited:

yesterdays

Active Member
Joined
Oct 8, 2014
Messages
337
And one more thing, your data in your main sheet seem wrong to what you need
should it be like this :
Audit 1Audit 2
Score 1Score 2Score 3Score 1Score 2Score 3
Case #Agent
1Smith553425
2Donna425235
3Jewel235444
4Fabian444421
5Renee421553

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,311
Messages
5,600,886
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top