I am creating a spreadsheet in EXCEL 2002.

In one of the calculation sheets in this spreadsheet, I will calculate
a row of numbers for 14 groups, i.e.,


Group 1     25   23  etc.
Group 2     10   20  etc.
.
.
.
Group 14    33  22  etc.


I would like to pull just one of these fourteen rows of data into
another row (which will be displayed on a separate page to the user)
depending on which of the fourteen groups is chosen by the user.  I
intend to provide a list of option buttons to allow the user to choose
which group he is interested in.

Can you provide an example of how to code this?  I've used the IF
statements in similar situations with only a few groups, but it seems
that there are too many groups to use this approach.  I'm sure there
must some way to work around this.   Thanks!

---

Request for Question Clarification by joey-ga on 15 Jan 2005 11:07 PST

Will there always be an identical number of columns for each row (i.e.
will there always be the same number of data points for each of the 14
rows?)  How many columns/data points will there be?

--Joey

---

Clarification of Question by skyone-ga on 15 Jan 2005 12:11 PST

Yes, there will always be the same number of data points (i.e.,
columns) for all groups (i.e., rows).  Depending on the data that is
entered in other parts of the spresdsheet, there will be either 2, 3
4, or 5 data points for every row.    When there is less than 5 data
points per row, the columns not used will be "blank".

---

Request for Question Clarification by endo-ga on 15 Jan 2005 13:52 PST

Hi,

How do you want the user to interact (choose the row) within the spreadsheet?

What you want can probably be done in VBA rather quickly.

Thanks.
endo

---

Request for Question Clarification by joey-ga on 15 Jan 2005 14:00 PST

Sorry, last question.  Are you already familiar with how to create the
user-interaction part of it, or do you need information on that too?

Assuming you're already familiar with how to create the buttons,
drop-down lists, etc., the formula to pull the data points will be a
relatively simple usage of the VLOOKUP function.  If all you need is
the formula, I'll be happy to answer with instructions.

--Joey

---

Clarification of Question by skyone-ga on 15 Jan 2005 14:10 PST

Yes, I know how to create buttons, drop-down lists, etc.  

The only thing I'm looking for is how to pull one row of data (from 14
rows) into another row on another sheet.  The row that will be pulled
will be determined by a cell with numbers ranging from 1 to 14.

Let me know if you need anything else. Thanks!

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 extra-complicated 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

---

Request for Answer Clarification by skyone-ga on 16 Jan 2005 08:35 PST

Thanks for your response.   I?m still a little bit unclear with the
more complicated formulas.  I?ll try to be more specific with my
example and hope that formulas based on my example will help me
understand what you?re doing a little better.  (I'm uncertain about
whether the " " are meant to actually be included in the formula or
are meant to indicate that I should put a value in there.)

My data will be located in "sheet 2" and will look like this:
	
	A	B	C	D	E	F	G
1	1	4.0%	8.2%	2.5%	7.2%		40.8%
2	2	4.0%	8.1%	2.6%	7.1%		41.7%
3	3	4.0%	8.4%	2.4%	7.4%		39.8%
4	4	5.2%	9.1%	3.2%	9.0%		30.1%
5	5	3.9%	8.0%	2.2%	7.0%		40.1%
6	6	3.5%	8.0%	2.5%	6.5%		48.3%
7	7	5.6%	8.9%	2.9%	8.2%		31.5%
8	8	3.9%	8.1%	2.4%	7.1%		40.8%
9	9	3.5%	7.6%	2.7%	6.5%		48.3%
10	10	4.8%	9.2%	3.4%	9.7%		28.7%
11	11	4.0%	7.9%	2.1%	6.9%		39.1%
12	12	3.5%	8.6%	2.1%	6.5%		48.2%
13	13	4.6%	8.8%	2.7%	8.1%		34.6%
14	14	4.8%	8.7%	2.8%	7.8%		33.9%


The group id is in the first column (i.e., column A).   I?ll have
another cell in the same worksheet which will have the number of the
group that should be pulled (for the purpose of this explanation, I?ll
say that it will be in cell A20 of the "sheet 2").

Depending on which group number is located in A20, I?d like that row
of data (excluding the look-up numbers in column A) to be pulled into
my "presentation row" in another sheet that I'll call "sheet 1".  In
this example, the number 12 will be in cell A20 and so the data in
that row should be placed in the following cells in "sheet 1", i.e.,

	  B       C      D       E      F     G		
    3 	 3.5%	8.6%	2.1%	6.5%	-   48.2%


If there are blanks in the original data, I?d like a dash (i.e., ?-?)
to be put in the final row.

I realize that I?m asking for some additional work for this so if you
can give me the code for this example, I can offer an additional $10. 
 Thanks!

---

Clarification of Answer by joey-ga 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 index-lookup 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
A-G 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 1-14 and then a "12" is sitting at the end out of
order.  This breaks the formula.  For this example, I've moved your
index-lookup cell to I20 on Sheet2.

That is the only change necessary on Sheet2.

Then, in cells A3-F3 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