How to auto populate other cells when selecting values in Excel drop down list?

Hello All,

I have a Data Sheet with Accountant Details for all clients in columns A to F.

The Column headings are Accountant Firm Nane, Accountant, Company Name, Exposure, Account amanger etc.

The goal is to have a drop down menu from the Accountant Firm Name list, where once a firm is selected all the other Data in B:F shows.
The problem is when I make the drop down list, the lsit displays multiple entries of the same name because there are repeats in the list (column A) this is a sample Sheet in reality I have 1000 rows so a lot more repeats, I need a way around this to show unique entries in drop down only, otherwise it is too redundant.

For Ex:
User Selects Drop Down Menu of an Accounting Firm "ABC Accounting"

All of a sudden data populates the sheet:

Accountant Client Name ID # Exposure Account Manager

Paul Bryant Openlane 54251 $120,215 John Doe
Paul Bryant Yearin 69825 $882,114 Paul Awal
Paul Bryant Goodsilron 24546 $512,545 Sabrina Laury
Paul Bryant Condax 32675 $58,216 John Doe
John Smith Opentech 21548 $654,542 Paul Awal
John Smith Golddex 54987 $31,324 Sabrina Laury
Luke Ryan year-job 23355 $854,515 John Doe
Sydney Samps Isdom 22222 $213,454 Sabrina Laury

This can be done on a new sheet so it's clean or on the side and I will figure it out. Please view my Attachment of the sample workbook.
your comments will help me out a lot. How do I do this?

Hey Richard,

I sent you a Private Message for one addition I would love to do to the Workbook I would a appreciate it a lot if you got back to me and uploaded your workbook here!

Thank you for your kind help !

Thank you for your kind help Richard!

Hi Glenn -
I am trying to understand why the "List" does not pick up companies after row 37? "=Sheet1!$A$4:INDEX(Sheet1!$A$2:$A$1000,COUNTA(Sheet1!$A$4:$A$1000))" how would I change this to search past row 37. Appreciate the model's functionality, just trying to understand how it works / how to modify it. Thank you for your time.
Regards,
Matt

Hi Richard,
Thank you for that slicer view. I ultimately am trying to understand excel more and eventually VBA as the data set I am working with will require more than slicers can provide.
Example. I am doing a daily data pull of all sales opportunities from a Salesforce report that cover the dept I work in, hundreds of rows of opportunities. I export the report into excel and then combining each day this data set. What I ultimately want to do is learn to write code that allows me to be informed when there is a change between data pulls of an opportunity. For example the stage moves from prospect to qualified. Also to understand if the value changes, etc. So I am in effect starting on a path to get there. Do you have any VBA books you recommend?