Good Morning Excel Experts...
I posted this earlier but received no responses....please point me in the right direction !!!
I am building a fairly basic Excel database....yes, I could probably do this in Access but I want to learn Excel and this is good practice.
I have 3 sheets in this workbook.
Sheet 1 is the CompanyInfo sheet and has all the different companies, (no duplicates) and their addreeses, phone#, etc.
Sheet 2 is for company contacts. Each company can have many contacts. The user will select from a data validation dropdown based on the list of companies from sheet 1. The list is a dynamically named range. So the user first selects the company and then inputs the company contact info; name, phone#, fax#, etc.
Sheet 3 is the transaction sheet. It is used to record transactions. Here is where I'm getting bogged down. What I would like to be able to do is have the user, in Col A, select a company via data validation dropdown, then in Col B have a dropdown that would have all the contacts for a given company. Then the rest of the transaction data would follow after that.
Some months back I posted a question regarding "cascading combo boxes" and someone showed me a method using the data validation dropdown with a formula using the indirect function to populate the box. That's fine if you only have a few companies and your contacts are fairly static. However, I have probably over 500 companies to set up. I have thought about putting a button that would sort the contacts sheet alpha by company each time a user inputs a new contact record so at least the contacts would all be in a contiguous range however, I don't think a dynamic range will work because there would not be a absolute point to anchor the range. My supervisor who is very good with Access but less so with Excel advised that maybe some type of filter event tied to and dependant on Col A where the specific company would be selected.
I apologize for the lengthy post but I wanted to give as much detail as possible.
Thanks,
Joe Dowski
Oxford, CT. USA.
I posted this earlier but received no responses....please point me in the right direction !!!
I am building a fairly basic Excel database....yes, I could probably do this in Access but I want to learn Excel and this is good practice.
I have 3 sheets in this workbook.
Sheet 1 is the CompanyInfo sheet and has all the different companies, (no duplicates) and their addreeses, phone#, etc.
Sheet 2 is for company contacts. Each company can have many contacts. The user will select from a data validation dropdown based on the list of companies from sheet 1. The list is a dynamically named range. So the user first selects the company and then inputs the company contact info; name, phone#, fax#, etc.
Sheet 3 is the transaction sheet. It is used to record transactions. Here is where I'm getting bogged down. What I would like to be able to do is have the user, in Col A, select a company via data validation dropdown, then in Col B have a dropdown that would have all the contacts for a given company. Then the rest of the transaction data would follow after that.
Some months back I posted a question regarding "cascading combo boxes" and someone showed me a method using the data validation dropdown with a formula using the indirect function to populate the box. That's fine if you only have a few companies and your contacts are fairly static. However, I have probably over 500 companies to set up. I have thought about putting a button that would sort the contacts sheet alpha by company each time a user inputs a new contact record so at least the contacts would all be in a contiguous range however, I don't think a dynamic range will work because there would not be a absolute point to anchor the range. My supervisor who is very good with Access but less so with Excel advised that maybe some type of filter event tied to and dependant on Col A where the specific company would be selected.
I apologize for the lengthy post but I wanted to give as much detail as possible.
Thanks,
Joe Dowski
Oxford, CT. USA.
