Solved

Populate data into a sheet - using data valuation selection

Posted on 2016-08-16
3
39 Views
Last Modified: 2016-11-22
Hi Experts using Excel 2013

Not sure how to do this but need a formula or macro (better if this can be done by formula) to do the following:

Sheet Master

Cell B1 Whatever the user selects here based on the data in the data sheet I want to populate the employee names from the data sheet into sheet Master cell A3 onwards.

So if I select Apple pull back all the apple employee name from the data sheet and likewise if I select Dell in Cell B1 sheet Master populate the employee names for the Dell workers.
Test-123.xlsx
0
Comment
Question by:route217
  • 2
3 Comments
 
LVL 30

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41758956
On Master Sheet, in A3, try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

In A3
=IFERROR(INDEX(Data!$B$2:$B$20,SMALL(IF(Data!$A$2:$A$20=$B$1,ROW(Data!$A$2:$A$20)-ROW(Data!$A$2)+1),ROWS(A$3:A3))),"")

Open in new window

And copy down until you get blank cells.

For details, refer to the attached.
Test-123.xlsx
1
 

Author Comment

by:route217
ID: 41758973
Many thanks for the excellent feedback let me check.
0
 

Author Comment

by:route217
ID: 41759445
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

828 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