As I mentioned before, the key here is the "VLOOKUP" function which
will search a series of cells in one column for a reference and then
can return a data point from another column on the selected row.
First, on the spreadsheet that contains the groups, it will look
something like this:
A B C D E F
1 Group 1 25 23 18 10 35
2 Group 2 10 20
3 Group 3 33 22 15 18
Assume it's on a sheet called "data" (the tab at the bottom of the
screen says "data" for this sheet).
Then, on your main spreadsheet, say it looks something like this:
A B C D E F
1 Group # 3
2
3 Row 33 22 15 18
In B3 through F3, you would put the following formulas:
B3:
=IF(VLOOKUP(CONCATENATE("Group
",$B$1),data!$A:$F,2)="","",VLOOKUP(CONCATENATE("Group
",$B$1),data!$A:$F,2))
C3:
=IF(VLOOKUP(CONCATENATE("Group
",$B$1),data!$A:$F,3)="","",VLOOKUP(CONCATENATE("Group
",$B$1),data!$A:$F,3))
D3:
=IF(VLOOKUP(CONCATENATE("Group
",$B$1),data!$A:$F,4)="","",VLOOKUP(CONCATENATE("Group
",$B$1),data!$A:$F,4))
E3:
=IF(VLOOKUP(CONCATENATE("Group
",$B$1),data!$A:$F,5)="","",VLOOKUP(CONCATENATE("Group
",$B$1),data!$A:$F,5))
F3:
=IF(VLOOKUP(CONCATENATE("Group
",$B$1),data!$A:$F,6)="","",VLOOKUP(CONCATENATE("Group
",$B$1),data!$A:$F,6))
These formulas are extracomplicated because they allow for blank
cells. In its simplest form, the formula would only need to be:
B3: =VLOOKUP(CONCATENATE("Group ",$B$1),data!$A:$F,2)
But, that alone will convert a "blank" cell to a "0" when it pulls it
in. So, my more complicated version above will maintain the blank
attribute when transfered.
Looking into what the simpler formula does (you an extrapolate out to
see how it would work with the IF statement), it:
1. Takes the row # you're looking for and tacks the word "Group" before it
2. Searches the "data" worksheet for a row named "Group XX"
3. Returns the value of the cell in the 2nd column on that row
This repeats for the other four cells, only it pulls column 3, 4, 5, or 6.
Please let me know if you have any questions.
Joey
Strategy: person knowledge of Excel
For more information in Google, search for: excel vlookup 
Clarification of Answer by
joeyga
on
16 Jan 2005 10:23 PST
All of the quotes were necessary. Unfortunately Google Answers
wrapped the lines and so some of the spacing was messed up.
One thing has to change in your example: the indexlookup cell in A20
has to be moved elsewhere. In fact, it can go in any place except
column A, but I would recommend that you put it in a column OTHER than
AG because you may want to add more data to those columns later, and
this cell could get in the way. It can't go in A, because that's the
column that VLOOKUP is searching for a value. VLOOKUP requires that
the values be sorted numerically, and to it, it sees a series of
numbers ranging from 114 and then a "12" is sitting at the end out of
order. This breaks the formula. For this example, I've moved your
indexlookup cell to I20 on Sheet2.
That is the only change necessary on Sheet2.
Then, in cells A3F3 on Sheet1, enter the following:
A3:
=IF(VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,2)="","",VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,2))
B3:
=IF(VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,3)="","",VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,3))
C3:
=IF(VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,4)="","",VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,4))
D3:
=IF(VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,5)="","",VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,5))
E3:
=IF(VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,6)="","",VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,6))
F3:
=IF(VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,7)="","",VLOOKUP(Sheet2!$I$20,Sheet2!$A:$G,7))
In case these get wrapped again unnecessarily, there should be *no*
spaces anywhere in them, and all of the quotes are necessary.
I expect that this should serve your purposes, but if you would like
anything further explained, please let me know.
Joey
