Display data in sht1 using combobox & sht2

Microsoft Office 2003 small business
September 22, 2009 at 08:16:57
Specs: Windows XP
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.


See More: Display data in sht1 using combobox & sht2

Report •

September 23, 2009 at 06:02:33

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 A
The 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

In sheet 1 you have a drop-down list in cell A2 that contains the headings from Row 1

You can now select the name of a Canadian Province from your list.

On selecting the name, the relevant data for that Province appears

On 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 1
The Description for the results is in column A, rows 5 to 7
The selected results are in column B, rows 5 to 7

Use the following formulas
In 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 match

Here is the output on sheet 1 when Ontario is selected in cell A2

1	Select	
2	Ontario	

	A			B
5	Capital			Toronto
6	Population		13.0M
7	Capital population	5.1M


Report •
Related Solutions

Ask Question