Turning a large flat file into several drop downs

[gameplayer]

Hello,
I'm using Office 2016. Please see the attached sheet. The first tab is what I would like to have but I can't figure out how to get it working. The second sheet is the old style flat file from which the drop downs should pull. Does anyone have a suggestion? Thanks in advance

[Richard Buttrey]

Hi,

Not entirely sure what you want to happen once you have picked a value from the drop down, or indeed which columns in the Data represent the audit type or auditor. Assuming column A is the carrier, name A2:A181 'carrier'

In the carrier drop down use the List option within the Data Validation menu and enter
=carrier

[gameplayer]

On the tab called Data sheet I have the following

Column A = a list of 181 carriers
Column B = Auto claims with the auditor listed for each of the 181 carriers.
Column C = Commercial Property, etc

What I want to happen is that the user uses a drop down to select a carrier, then uses a second drop down to select the claim type, and a third cell will display the auditor for that carrier and that claim type

[Richard Buttrey]

OK, if it's dependent drop down lists that you want I can do no better than point you to Debra's web site where this subject is covered in all its glory.
See here http://www.contextures.com/xlDataVal02.html

[gameplayer]

I've looked at that website as you suggested but I don't believe it will work. The problem is that the data she is using isn't interdependent. My table is more like a multiplication table.

On the tab called Data Sheet, Cell B2 shows the name of the auditor (Steve) for the carrier named AAA and the file type Auto.
Cell B3 shows the auditor Chris for the carrier AAA and file type Commercial Property.

Her list of named ranges are for things like fruits and vegetables. They do not relate to each other. I do not believe that I can just give each column on the Data Sheet a named range and make it work

Thanks for your help

[gameplayer]

Data Sheet - I have defined a name for the carrier column of 181 entries and I have defined a name for the file types: auto, commercial, etc.
Search sheet - I have used data validation on cells B6 and D6 linking them to the previously mentioned named ranges. Now what? I've attached the file with the new changes

[ChemistB]

In F6

=INDEX('Data sheet'!$B$3:$M$182,MATCH($B$6,'Data sheet'!$A$3:$A$182,0),MATCH($D$6, Audit_Type,0))

[gameplayer]

That works wonderfully! Thank you. You just made 50 employee's lives easier. Thanks again!