Index/Match/Vlookup

[ajaffer]

Hi, i need some help constructing a formula.

I have two sheets, the first one containing imported data. the second i have to construct.

i need to construct a formula that when a user enter's a season number, the data will be retrieved from the 1st sheet and displayed in the second sheet. this is with regards to a tv-show.

the data worksheet contains:
Title, Season, AirDate,StarDate,Synopsis...

Title is sorted alphabetically.

Thanks

 

[Oaktree]

Welcome to the board.

Can you post a sample of your data and what you'd like the results to look like?

(See "download Colo's HTML Maker..." at the bottom of this page)

 

[ajaffer]

I can't get HTML Maker to work on my pc.

 

[Oaktree]

Did you try Kristy's suggestions here:

http://www.mrexcel.com/board2/viewtopic.php?t=92622
?

 

[ajaffer]

This is the data:

star trek.xls
	A	B	C	D	E
1	Title	Season	Air Date	Stardate	Synopsis
2	11001001	1	1/2/1988	41365.9	The Bynars take control of Enterprise.
3	A Fistful of Datas	6	9/11/1992	46271.5	Data becomes the villain in a Holodeck malfunction.
4	A Matter of Honor	2	6/2/1989	42506.5	Riker serves as first officer on board a Klingon ship.
5	A Matter of Perspective	3	12/2/1990	43610.4	Riker is accused of murder.
6	A Matter of Time	5	18/11/1991	45349.1	The Enterprise plays host to a visitor from the future.
7	All Good Things... Part I	7	23/05/1994	47988.1	Picard tries to prevent the destruction of humanity.
8	All Good Things... Part II	7	23/05/1994	47988.1	Picard tries to prevent the destruction of humanity.
9	Allegiance	3	26/03/1990	43714.1	A replica of Picard replaces him on the Enterprise.
10	Angel One	1	25/01/1988	41636.9	The crew travels to a planet with a matriarchal society.
11	Aquiel	6	1/2/1993	46461.3	Geordi falls in love with an alien murder suspect.
12	Attached	7	8/11/1993	47304.2	Picard and Beverly face their feelings for each other.
13	Birthright, Part I	6	22/02/1993	46578.4	Worf and Data set out on journeys to find their fathers.
14	Birthright, Part II	6	1/3/1993	46579.2	Worf risks his life to inspire younger Klingons.
15	Bloodlines	7	2/5/1994	47829.1	Picard learns he has a son.
16	Booby Trap	3	30/10/1989	43205.6	The Enterprise is ensnared in an intergalactic booby trap.
17	Brothers	4	8/10/1990	44085.7	Data faces his creator and his evil brother, Lore.
18	Captain's Holiday	3	2/4/1990	43745.2	Picard's vacation is interrupted by time travelers.
19	Cause and Effect	5	23/03/1992	45652.1	The Enterprise is trapped in a time warp.
20	Chain of Command, Part I	6	14/12/1992	46357.4	After being reassigned, Picard is taken hostage.
21	Chain of Command, Part II	6	21/12/1992	46360.8	The crew attempts to rescue Picard from Cardassians.
22	Clues	4	11/2/1991	44502.7	Data lies to the crew.
23	Code of Honor	1	12/10/1987	41235.25	Tasha is kidnapped.
24	Coming of Age	1	14/03/1988	41461.2	Wesley takes the grueling Starfleet Academy entrance exam.
25	Conspiracy	1	9/5/1988	41775.5	Picard suspects conspiracy in Starfleet.
26	Contagion	2	20/03/1989	42609.1	The Enterprise computer is infected by alien hardware.
27	Conundrum	5	17/02/1992	45494.2	Mass amnesia strikes the Enterprise crew.
28	Cost of Living	5	20/04/1992	45733.6	Lwaxana introduces Alexander to her wild lifestyle.
29	Dark Page	7	1/11/1993	47254.1	Lwaxana has a psychic breakdown.
30	Darmok	5	30/09/1991	45047.2	Picard deals with an alien who speaks in metaphors.
StarTrek

 

[ajaffer]

This is the worksheet i have to construct:

star trek.xls
	A	B	C	D	E
1	Star Trek Episode Searcher
2
3	Enter Season Number:				Please Enter Another Season Number.
4	Search Results Found:		No Results
5
6	Episode Details
7	SequenceId	Title	Air Date	Stardate	Synopsis
8
9
10
11
12
13
Searcher

 

[Oaktree]

One way is to use advanced filter.

Put the word "Season" (without the quotes) in cell H1
H2 will be the data entry cell for the season

go to data --> filter --> advanced filter. Set list range =A1:F18, criteria range = H1:H2, and output range of J1:O1 or wherever you'd like the output.

That will get you most of the way to your results.

You can use the worksheet_change in VBA to trigger the filter whenever the value in H2 changes or to cut & paste the results to the other sheet (if you need to).

(Remember that you can use the macro recorder if you're not sure what the code is to reference the advanced filter...)

 

[ajaffer]

I tried the advance filter, however, it doesnt automatically change when i try to change the season number. I need to create a search model (database) using lookup functions: index,match,vlookup. Is it possible?

 

[ajaffer]

help

please help me, ive been working on this for the last 4 hours. im gettin suicidal now. driving me nuts.