New Member
Dec 2, 2005
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.


Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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)
Upvote 0
This is the data:
star trek.xls
1TitleSeasonAir DateStardateSynopsis
21100100111/2/198841365.9The Bynars take control of Enterprise.
3A Fistful of Datas69/11/199246271.5Data becomes the villain in a Holodeck malfunction.
4A Matter of Honor26/2/198942506.5Riker serves as first officer on board a Klingon ship.
5A Matter of Perspective312/2/199043610.4Riker is accused of murder.
6A Matter of Time518/11/199145349.1The Enterprise plays host to a visitor from the future.
7All Good Things... Part I723/05/199447988.1Picard tries to prevent the destruction of humanity.
8All Good Things... Part II723/05/199447988.1Picard tries to prevent the destruction of humanity.
9Allegiance326/03/199043714.1A replica of Picard replaces him on the Enterprise.
10Angel One125/01/198841636.9The crew travels to a planet with a matriarchal society.
11Aquiel61/2/199346461.3Geordi falls in love with an alien murder suspect.
12Attached78/11/199347304.2Picard and Beverly face their feelings for each other.
13Birthright, Part I622/02/199346578.4Worf and Data set out on journeys to find their fathers.
14Birthright, Part II61/3/199346579.2Worf risks his life to inspire younger Klingons.
15Bloodlines72/5/199447829.1Picard learns he has a son.
16Booby Trap330/10/198943205.6The Enterprise is ensnared in an intergalactic booby trap.
17Brothers48/10/199044085.7Data faces his creator and his evil brother, Lore.
18Captain's Holiday32/4/199043745.2Picard's vacation is interrupted by time travelers.
19Cause and Effect523/03/199245652.1The Enterprise is trapped in a time warp.
20Chain of Command, Part I614/12/199246357.4After being reassigned, Picard is taken hostage.
21Chain of Command, Part II621/12/199246360.8The crew attempts to rescue Picard from Cardassians.
22Clues411/2/199144502.7Data lies to the crew.
23Code of Honor112/10/198741235.25Tasha is kidnapped.
24Coming of Age114/03/198841461.2Wesley takes the grueling Starfleet Academy entrance exam.
25Conspiracy19/5/198841775.5Picard suspects conspiracy in Starfleet.
26Contagion220/03/198942609.1The Enterprise computer is infected by alien hardware.
27Conundrum517/02/199245494.2Mass amnesia strikes the Enterprise crew.
28Cost of Living520/04/199245733.6Lwaxana introduces Alexander to her wild lifestyle.
29Dark Page71/11/199347254.1Lwaxana has a psychic breakdown.
30Darmok530/09/199145047.2Picard deals with an alien who speaks in metaphors.
Upvote 0
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...)
Upvote 0
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?
Upvote 0

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

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back