Excel drop down lists

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:30 PM GMT

Question

I am having a really difficult time creating a sheet to operate how i need it to. What I have is a list of 200 properties, each property has a ton of data associated with it, i.e., address, time left on contract,total customers, total units, etc.... 
What I am attempting to do is create a very clean sheet, which would have basically one drop down cell, this cell would contain the list of all 200 properties, & once a user selected the appropriate property from the list, it would then display all of the information in a row, for that selected property (which would be on another sheet in the book. 
I have added a few macro's to the sheet, & have been able to create an actual live count down timer in my workbook (Thanks All Experts!!!!) which is great, however, I cannot for the life of me, figure out how to get this drop down function to perform correctly. 
I hope you can help, & for your time!

BW

Answer

you will need two sheets: 1.- put all the data, make sure to put those dropdown list values on the first column (A:A), and that every column have a heading (let's call this sheet "DATA") 2.- desing your template, put all information you want about the property(from now on TEMPLATE)

the Template sheet of my example have the dropdown list on cell B2, for that I have created a dropdown list from data validation along with a dynamic defined name I have prevously define that dynamic name with this formula following these steps: a. go to insert- name - define. b. on "refer to" field write this formula: =OFFSET(DATA!$A$1,1,0,COUNTA(DATA!$A:$A)-1,1)

Afterwards An cell A3, A4 and A5 of TEMPLATE I have the headings which I want to be looked up! And I have formulated B3 like this: =VLOOKUP($B$2,DATA!$A:$D,MATCH(A3,DATA!$1:$1,0),0) and then I copied it over the rest of the cells at right hand of the headings

Follow this step by step and you will have what you want. please follow up if you need further help

Advertisement

©2020 eLuminary LLC. All rights reserved.