Cell as reference anchor to another sheet

hi,

I have a problem how to do the next:
A1 cell contains title of some test
The next cells on bottom line (B2:B10) contains the results of the this test.

On another Sheet i want to analyze those results, thus I need the results from Sheet1 be copeid to sheet2. I want just to indicate the test name (A1 location) and the rest of the cells will be copeid automatically(relative reference, relatively to A1).
How can I do it?

Thanks!!!

Here's what I did.
First I named your data for each test. Names cannot have spaces so I named C2:E3 "test1", C6:E7 "test2",etc.

Then on Sheet 2, I selected E4:G6 and entered

Please Login or Register  to view this content.

entering it as an array (CNTRL + SHFT + ENTER). Then enter your test name in Sheet2!D3 and the appropriate results and graph are populated. See example.
If you do not have spaces in your test names, you can remove the SUBSTITUTE Function and the INDIRECT Function.

Based on your very first sample file.

Sheet2!E4: =INDEX(Sheet1!C:C,MATCH($D$3,Sheet1!$A:$A,0)+ROWS(E$4:E4))
copied across matrix E4:G5

This thiis,

=OFFSET(INDIRECT("Sheet1!"&ADDRESS(MATCH(Sheet2!$D$3,Sheet1!$A:$A,0),1)),$I4,E$7)

E7:G7 contains the values 1,2,3 which is the column offset
I4:I5 contains the values 1,2 which is the row offset

at which point we revert to the question in your other thread...

if you have zero means by which to determine which "test" you need how exactly do you expect Excel to be able to work this out ?

Think of your question as reading like:

"I've thought of a number between 1 and 100. Please tell me the number."

It's seemingly arbitrary... if the process is devoid of logic it can not be put into logical form.

So you're populating Sheet2!D3 by what means exactly ?
(ie to generate the link to Sheet1!A5)

You would need to use VBA if you wish to use the range reference in D3 as the base point for retrieving the other values given the value itself is not sufficient.

Here is a possible UDF approach for you

Please Login or Register  to view this content.

the above stored in a Module could be called from your cells along the lines of:

Please Login or Register  to view this content.

If you alter the link in D3 you should find the XY values update accordingly.