Solved

Help with populating cells via VLoopUp or Other Method (open to suggestions)

Posted on 2014-09-22
5
168 Views
Last Modified: 2014-09-23
Hi experts

I need to Excel expert to guide me on the best way to do something.

Part 1:
I want to populate cell B1 of the 'Main Page' worksheet with a Company Name from Company Names in the 'Contacts" worksheet. I've used a VLoopUp which returns my the company names, but there are a whole heap of duplicates, which as the list grows will be annoying. I need to filter this list down more by selecting a contact name from the company, so I'm not sure if the best approach is to have a drop-down list of the company names and the contact, or to filter the company name list down so no duplicates appear, and then under cell B5 have a selection of Contact Names that only appear from that selected Company Name.

Part 2:
What function/s should I use to achieve the solution to part 1, I.e. can you have a Vlookup that displays to columns, or can you stop showing duplicates in the Vlookup? And in Cell B5 can I filter the drop-down list to only show names connected with the selected Company Names.

Help! I've attached my file.
Populate-Address.xlsx
0
Comment
Question by:Fi69
  • 2
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40338545
There are a couple of ways one can go about this.  One way is to use VBA to create a list "on-the-fly" as data is changed.  Another way is to create dynamic ranges that will capture the unique data and present that in Data Validation.  I'm showing the latter method here.

First, to create a list of unique company names, an additional table will need to be created.  In it, the following formula would be inserted in the second row and copied down as far as needed:
=IFERROR(INDEX($A$2:$A$33,MATCH(0,INDEX(COUNTIF($G$1:G1,$A$2:$A$33),0),0)),"")

This will produce a list of unique values - company names - from your main table.  This is then made into an Excel Table and assigned the name "Table2".

Second, a dynamic range name is created to reference only the non-blank cells in this new table.  I'll call it "CompList" and give it the following formula:
=OFFSET(Contacts!$G$1,1,0,MATCH("",Table2,0)-1,1)

Third, we'll assign this range to the data validation in cell B1 of your "Main Page" worksheet.  Just change the Data Validation source to "=CompList".  Now, only the four companies are visible in the drop down.

Since only the company name is shown in cell B1, we'll need to show the valid contact names for the selected company in cell B5.  Again, we'll create a dynamic range name called "ContactList" and give it the following formula:
=OFFSET(Contacts!$A$1,MATCH('Main Page'!$B$1,CompanyName,0),3,COUNTIF(CompanyName,'Main Page'!$B$1),1)

Lastly, we'll assign this range name to the data validation for cell B5 (setting the Source to "=ContactList").  This ensures that only contacts aligned with the company selected in B1 will show up.

I've taken the liberty of removing the "Contacts" range name, as it was not needed in this example.  I've attached a modified file for your review.

Regards,
-Glenn
EE-Populate-Address.xlsx
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40338552
Please find attached.
Populate-Address.xlsx
0
 

Author Comment

by:Fi69
ID: 40338573
OMG Glen, that's fantastic. I'm going to look at it more closely now and try and understand the process.
0
 

Author Closing Comment

by:Fi69
ID: 40338737
Thank you!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40339565
You're welcome.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question