That means it will pull in columns 2 through 5 from the table and insert that data into the appropriate columns. Note that the column reference for the VLOOKUP formula is. Next, enter the formula noted below, and enter as an array formula: First, highlight all four cells I want the data to be entered in. Now, to pull in the data for the four locations for that selection, I need to use VLOOKUP in an array formula (enter with Ctrl + Shift + Enter) as follows. Note: I need to wrap the table and column reference in double quotes. The Data Validation drop down list works fine: So if I modify that source information with the following: The INDIRECT function "returns the reference specified by a text string". The solution is to that is to wrap the reference in an INDIRECT function. My table name is "CoSales", and the column I want to reference for my Data Validation drop down list is "Salesperson", so you would think that in my Data Validation source I could enter =CoSales[Salesperson} and it would create the correct list. Here I have a table with a list of salespeople and their results for four locations:īelow that I want to set up an area where I can use Data Validation to select a Salesperson and have the four cells populate their sales for the four locations: The previous tutorial about extracting multiple columns using VLOOKUP can be found here. When you get a preview, look for Download in the upper right hand corner. You can download the file here and follow along. We'll also include a couple other useful Excel tips. In this tutorial we will take a look at how to use a column from a table as the source for a Data Validation List.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |