# Display data in sht1 using combobox & sht2

September 22, 2009 at 08:16:57
Specs: Windows XP
 Hi I want to display information in sheet 1 using sheet 2 and combo box,so when combo box is selected (Area name) all the information regarding the selected area must be displayed on sht 1 under particular headings.I can't use Pivot table as there is no numerical data in the table.Thanks

See More: Display data in sht1 using combobox & sht2

#1
September 23, 2009 at 06:02:33
 Hi,You haven't given a lot of information, but let's assume that Sheet 2 contains columns of data, with headings in row 1 and descriptions in column AThe data itself including headings is in the range Sheet2!B1:E4``` A B C D E 1 Province Alberta Saskatchewan Manitoba Ontario 2 Capital Edmonton Regina Winnipeg Toronto 3 Population 3.5M 1.0M 1.2M 13.0M 4 Capital 1.03M 0.19M 0.69M 5.1M population ```In sheet 1 you have a drop-down list in cell A2 that contains the headings from Row 1You can now select the name of a Canadian Province from your list.On selecting the name, the relevant data for that Province appearsOn sheet 1 in cell A1 have the instruction 'Select'Cell A2 has a drop down list created with data validation - list of provinces (in this example)The results are shown on sheet 1The Description for the results is in column A, rows 5 to 7The selected results are in column B, rows 5 to 7Use the following formulasIn B5 put =HLOOKUP(\$A\$2,Sheet2!\$B\$1:\$E\$4,2,FALSE)In B6 put =HLOOKUP(\$A\$2,Sheet2!\$B\$1:\$E\$4,3,FALSE)In B7 put =HLOOKUP(\$A\$2,Sheet2!\$B\$1:\$E\$4,4,FALSE)Note that the formulas have the same lookup value (\$A\$2)the same table range Sheet2!\$B\$1:\$E\$4, but the number following this increments as it is the row index number or offset in the table for each item to be displayed. False just ensures that Hlookup must find a full matchHere is the output on sheet 1 when Ontario is selected in cell A2``` A 1 Select 2 Ontario A B 5 Capital Toronto 6 Population 13.0M 7 Capital population 5.1M ```Regards

Report •
Related Solutions