Pulling data by row into other sheets

[shrinkingnadia]

Hi, there!

I am trying to do something that might be simple but I can not think of how to word it to search for it elsewhere. Will try to explain it. . .

I want to have a front sheet with contents by row and additional sheets that will pull the data from those rows.

For example, front sheet:

Trial 1	Apples	Artichokes	Asparagus
Trial 2	Turtles	Tortillas	Togas	Tangerines

Sheet 1:

Apples	manual notes
Asparagus
Apricots

Sheet 2:

Turtles	manual notes
Togas
Tangerines

Right now, I have data for about 7 trials, but will be adding more throughout the year. I would like to simplify this as much as possible by creating a template/sheet that would pull certain information from the first sheet, but specific to that trial. So, Contents!A1 or such is simple enough manually, but is there a way to set a sheet so it will “know” to use Contents!A6 for the sixth trial?

For example, if I put a name into the new sheet like Trial6, would there be a way to have the formulas react to pull data from the corresponding row on the first sheet?

Basically, the front will have quick-reference information and the additional sheets will expand on each trials’s information, but it would be lovely if information only needed to be entered once.

 

[ExceLoki]

this should work for what you're going for
---------

Book1
	A	B
1	Apples	manual notes
2	Asparagus
3	Apricots
4	Artichokes
Trial 1

---------

Book1
	A	B
1	Turtles	manual notes
2	Togas
3	Tangerines
4	Tortillas
Trial 2

---------

Book1
	A	B
1	Bananas	manual notes
2	Boats
3	Bandanas
4	Bands
Trial 3

---------

Book1
	A	B	C	D	E
1	Trial 1	Apples	Asparagus	Apricots	Artichokes
2	Trial 2	Turtles	Togas	Tangerines	Tortillas
3	Trial 3	Bananas	Boats	Bandanas	Bands
Refrence

Cell Formulas
Range	Formula
B1:E3	B1	=TRANSPOSE(INDIRECT("'"&A1&"'!"&"a1:a4"))
Dynamic array formulas.