 View Question
Q: EXCEL -- selecting data from one of fourteen groups ( Answered ,   0 Comments ) Question
 Subject: EXCEL -- selecting data from one of fourteen groups Category: Computers > Software Asked by: skyone-ga List Price: \$10.00 Posted: 15 Jan 2005 09:44 PST Expires: 14 Feb 2005 09:44 PST Question ID: 457677
 ```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!``` Subject: Re: EXCEL -- selecting data from one of fourteen groups Answered By: joey-ga on 15 Jan 2005 14:31 PST Rated: ```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```
 skyone-ga rated this answer: and gave an additional tip of: \$10.00  