Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Query Named Range Return Single Column Value
Excel; from a named range table of teams by division (BU10, BU12, GU10...)
and their standings, I want to place the value of a particular team name in a separate sheet w/in the same workbook, who is in a certain division (BU10) with a standing = 1, in essence query the named range and return a scalar value similar to an SQL query. Named Range 'MasterTeams' Standing Division Team ------------------------------------ 1 BU10 Edwards 2 BU10 Smith 3 BU10 Rojas 1 GU10 Rattigant 2 GU10 Markel 3 GU10 Ucamp I want on another sheet containing the playoff bracket to have in a cell the BU10 team name that has a standing of 1. SQL: select top 1 team from MasterGames where division = 'bu10' and standing = 1 TIA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Query Named Range Return Single Column Value
Jeff,
=INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet Name'!$B$1:$B$100="BU10")*ROW($B$1:$B$100))) The BU10 can be in a cell and referenced in the formula - to make it easier to create a table: =INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet Name'!$B$1:$B$100=A3)*ROW($B$1:$B$100))) HTH, Bernie MS Excel MVP "JeffP-" wrote in message ... Excel; from a named range table of teams by division (BU10, BU12, GU10...) and their standings, I want to place the value of a particular team name in a separate sheet w/in the same workbook, who is in a certain division (BU10) with a standing = 1, in essence query the named range and return a scalar value similar to an SQL query. Named Range 'MasterTeams' Standing Division Team ------------------------------------ 1 BU10 Edwards 2 BU10 Smith 3 BU10 Rojas 1 GU10 Rattigant 2 GU10 Markel 3 GU10 Ucamp I want on another sheet containing the playoff bracket to have in a cell the BU10 team name that has a standing of 1. SQL: select top 1 team from MasterGames where division = 'bu10' and standing = 1 TIA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Query Named Range Return Single Column Value
I'm sorry to be so dense....
Here's a better rendition of my named range, however using your formula, I'm referencing the sheet name, 'Master Playoff Teams', Note I have many more rows so I padded out to 1k, and other columns that I'm not concerned with, however these are in fact the first three columns A, B, C. A B C row Standing Division Team 1 1 BU10 Edwards 2 2 BU10 Smith 3 3 BU10 Rojas 4 1 GU10 Rattigant 5 2 GU10 Markel 6 3 GU10 Ucamp This is my attempt to replicate the function you provided, however it results in a #Ref msg. =INDEX('Master Playoffs Teams'!$A$1:$H$1000,SUMPRODUCT(('Master Playoffs Teams'!$A$1:$A$1000=1)*('Master Playoffs Teams'!$B$1:$B$1000="BU10")*ROW($B$1:$B$1000))) Now, face to face with the second step like a chihuahua on the steps of the capital I'm looking straight ahead and all I see is a wall... "Bernie Deitrick" wrote: Jeff, =INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet Name'!$B$1:$B$100="BU10")*ROW($B$1:$B$100))) The BU10 can be in a cell and referenced in the formula - to make it easier to create a table: =INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet Name'!$B$1:$B$100=A3)*ROW($B$1:$B$100))) HTH, Bernie MS Excel MVP "JeffP-" wrote in message ... Excel; from a named range table of teams by division (BU10, BU12, GU10...) and their standings, I want to place the value of a particular team name in a separate sheet w/in the same workbook, who is in a certain division (BU10) with a standing = 1, in essence query the named range and return a scalar value similar to an SQL query. Named Range 'MasterTeams' Standing Division Team ------------------------------------ 1 BU10 Edwards 2 BU10 Smith 3 BU10 Rojas 1 GU10 Rattigant 2 GU10 Markel 3 GU10 Ucamp I want on another sheet containing the playoff bracket to have in a cell the BU10 team name that has a standing of 1. SQL: select top 1 team from MasterGames where division = 'bu10' and standing = 1 TIA |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Query Named Range Return Single Column Value
Jeff,
You are referencing too large a range in your first range. This: =INDEX('Master Playoffs Teams'!$A$1:$H$1000..... should be =INDEX('Master Playoffs Teams'!$C$1:$C$1000..... Well, that is, unless you want to pull data from a column other than C - in which case you would need a fourth argument to select the column number within the A:H that you originally had: this will pull from column C. (This is a useful technique when you want to extract a table.) =INDEX('Master Playoffs Teams'!$A$1:$H$1000,SUMPRODUCT(('Master Playoffs Teams'!$A$1:$A$1000=1)*('Master Playoffs Teams'!$B$1:$B$1000="BU10")*ROW($B$1:$B$1000)),3) Note the ,3 at the end of the formula. HTH, Bernie MS Excel MVP "JeffP-" wrote in message ... I'm sorry to be so dense.... Here's a better rendition of my named range, however using your formula, I'm referencing the sheet name, 'Master Playoff Teams', Note I have many more rows so I padded out to 1k, and other columns that I'm not concerned with, however these are in fact the first three columns A, B, C. A B C row Standing Division Team 1 1 BU10 Edwards 2 2 BU10 Smith 3 3 BU10 Rojas 4 1 GU10 Rattigant 5 2 GU10 Markel 6 3 GU10 Ucamp This is my attempt to replicate the function you provided, however it results in a #Ref msg. =INDEX('Master Playoffs Teams'!$A$1:$H$1000,SUMPRODUCT(('Master Playoffs Teams'!$A$1:$A$1000=1)*('Master Playoffs Teams'!$B$1:$B$1000="BU10")*ROW($B$1:$B$1000))) Now, face to face with the second step like a chihuahua on the steps of the capital I'm looking straight ahead and all I see is a wall... "Bernie Deitrick" wrote: Jeff, =INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet Name'!$B$1:$B$100="BU10")*ROW($B$1:$B$100))) The BU10 can be in a cell and referenced in the formula - to make it easier to create a table: =INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet Name'!$B$1:$B$100=A3)*ROW($B$1:$B$100))) HTH, Bernie MS Excel MVP "JeffP-" wrote in message ... Excel; from a named range table of teams by division (BU10, BU12, GU10...) and their standings, I want to place the value of a particular team name in a separate sheet w/in the same workbook, who is in a certain division (BU10) with a standing = 1, in essence query the named range and return a scalar value similar to an SQL query. Named Range 'MasterTeams' Standing Division Team ------------------------------------ 1 BU10 Edwards 2 BU10 Smith 3 BU10 Rojas 1 GU10 Rattigant 2 GU10 Markel 3 GU10 Ucamp I want on another sheet containing the playoff bracket to have in a cell the BU10 team name that has a standing of 1. SQL: select top 1 team from MasterGames where division = 'bu10' and standing = 1 TIA |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Query Named Range Return Single Column Value
Bernie, I've spent alot of time, I'm not going to understand how multiplying
the values gets a result, but here's where I'm at, I've made some pseudo data to simplify... Consider the following data on Sheet1, I know I left out the *($B8:$B10) due it causing a #Ref msg; anyway - at least I'm getting some results that I can make adjustments to. A B C 1 BU10 Andy 2 BU10 Betty 3 BU10 Janet =INDEX(Sheet1!$C$8:$C$10,SUMPRODUCT((Sheet1!$A8:$A 10=1)*(Sheet1!$B8:$B10="BU10"))) Returns Andy, however the following also returns Andy A B C 2 BU10 Andy 3 BU10 Betty 1 BU10 Janet I would like it to return Janet. My next issue is that the teams are arranged in Pools, So for 10 teams there are two pools with standings 1 - 5, so in my first query there are two teams BU10 with a standing of 1, one in Pool A and the other in Pool B. "Bernie Deitrick" wrote: Jeff, You are referencing too large a range in your first range. This: =INDEX('Master Playoffs Teams'!$A$1:$H$1000..... should be =INDEX('Master Playoffs Teams'!$C$1:$C$1000..... Well, that is, unless you want to pull data from a column other than C - in which case you would need a fourth argument to select the column number within the A:H that you originally had: this will pull from column C. (This is a useful technique when you want to extract a table.) =INDEX('Master Playoffs Teams'!$A$1:$H$1000,SUMPRODUCT(('Master Playoffs Teams'!$A$1:$A$1000=1)*('Master Playoffs Teams'!$B$1:$B$1000="BU10")*ROW($B$1:$B$1000)),3) Note the ,3 at the end of the formula. HTH, Bernie MS Excel MVP "JeffP-" wrote in message ... I'm sorry to be so dense.... Here's a better rendition of my named range, however using your formula, I'm referencing the sheet name, 'Master Playoff Teams', Note I have many more rows so I padded out to 1k, and other columns that I'm not concerned with, however these are in fact the first three columns A, B, C. A B C row Standing Division Team 1 1 BU10 Edwards 2 2 BU10 Smith 3 3 BU10 Rojas 4 1 GU10 Rattigant 5 2 GU10 Markel 6 3 GU10 Ucamp This is my attempt to replicate the function you provided, however it results in a #Ref msg. =INDEX('Master Playoffs Teams'!$A$1:$H$1000,SUMPRODUCT(('Master Playoffs Teams'!$A$1:$A$1000=1)*('Master Playoffs Teams'!$B$1:$B$1000="BU10")*ROW($B$1:$B$1000))) Now, face to face with the second step like a chihuahua on the steps of the capital I'm looking straight ahead and all I see is a wall... "Bernie Deitrick" wrote: Jeff, =INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet Name'!$B$1:$B$100="BU10")*ROW($B$1:$B$100))) The BU10 can be in a cell and referenced in the formula - to make it easier to create a table: =INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet Name'!$B$1:$B$100=A3)*ROW($B$1:$B$100))) HTH, Bernie MS Excel MVP "JeffP-" wrote in message ... Excel; from a named range table of teams by division (BU10, BU12, GU10...) and their standings, I want to place the value of a particular team name in a separate sheet w/in the same workbook, who is in a certain division (BU10) with a standing = 1, in essence query the named range and return a scalar value similar to an SQL query. Named Range 'MasterTeams' Standing Division Team ------------------------------------ 1 BU10 Edwards 2 BU10 Smith 3 BU10 Rojas 1 GU10 Rattigant 2 GU10 Markel 3 GU10 Ucamp I want on another sheet containing the playoff bracket to have in a cell the BU10 team name that has a standing of 1. SQL: select top 1 team from MasterGames where division = 'bu10' and standing = 1 TIA |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Query Named Range Return Single Column Value
Jeff,
The way that SUMPRODUCT is being used in my formula is to return the ROW number of the matched data - your formula returns the COUNT of the matches, so you are always picking up the first value in column C (because you have only 1 match). Look at the parts of the SP formula: SUMPRODUCT((Sheet1!$A8:$A10=1)*(Sheet1!$B8:$B10="B U10"))) This part: Sheet1!$A8:$A10=1 returns an array of True / False values - in your example {True, False, False} This part Sheet1!$B8:$B10="BU10" similary returns {True, True, True} When thse are mutliplied together, you get {1,0,0}, and when SUMmed, you get 1. Therefore you always get the value in the first row of the range C8:C10 - or Andy. Now, if you had included the ROW part, that would return an array {8,9,10} and you would have mulitplied {1,0,0} by {8,9,10} to get the array {8,0,0}, which SUMs to 8. But, what would that get you? The value in the 8th row of C8:C10, which doesn't exist - since it is only three rows high!! That is why you are getting the REF! error. So, what do you do? Well, you could use a range that starts at ROW 1 - like I used in ALL of my examples. So TRY this with your example table: =INDEX(Sheet1!$C$1:$C$10,SUMPRODUCT((Sheet1!$A$8:$ A$10=1)*(Sheet1!$B$8:$B$10="BU10")*ROW($B$8:$B$10) )) Note that only the column C address needs to start at 1, though this will work as well =INDEX(Sheet1!$C$1:$C$10,SUMPRODUCT((Sheet1!$A$1:$ A$10=1)*(Sheet1!$B$1:$B$10="BU10")*ROW($B$1:$B$10) )) AND! You could also use this =INDEX(Sheet1!$C$8:$C$10,SUMPRODUCT((Sheet1!$A$8:$ A$10=1)*(Sheet1!$B$8:$B$10="BU10")*ROW($B$8:$B$10) )-7) with the -7 there to account for the indexed range starting at row8 and only consisting of 3 rows. HTH, Bernie MS Excel MVP "JeffP-" wrote in message ... Bernie, I've spent alot of time, I'm not going to understand how multiplying the values gets a result, but here's where I'm at, I've made some pseudo data to simplify... Consider the following data on Sheet1, I know I left out the *($B8:$B10) due it causing a #Ref msg; anyway - at least I'm getting some results that I can make adjustments to. A B C 1 BU10 Andy 2 BU10 Betty 3 BU10 Janet =INDEX(Sheet1!$C$8:$C$10,SUMPRODUCT((Sheet1!$A8:$A 10=1)*(Sheet1!$B8:$B10="BU10"))) Returns Andy, however the following also returns Andy A B C 2 BU10 Andy 3 BU10 Betty 1 BU10 Janet I would like it to return Janet. My next issue is that the teams are arranged in Pools, So for 10 teams there are two pools with standings 1 - 5, so in my first query there are two teams BU10 with a standing of 1, one in Pool A and the other in Pool B. "Bernie Deitrick" wrote: Jeff, You are referencing too large a range in your first range. This: =INDEX('Master Playoffs Teams'!$A$1:$H$1000..... should be =INDEX('Master Playoffs Teams'!$C$1:$C$1000..... Well, that is, unless you want to pull data from a column other than C - in which case you would need a fourth argument to select the column number within the A:H that you originally had: this will pull from column C. (This is a useful technique when you want to extract a table.) =INDEX('Master Playoffs Teams'!$A$1:$H$1000,SUMPRODUCT(('Master Playoffs Teams'!$A$1:$A$1000=1)*('Master Playoffs Teams'!$B$1:$B$1000="BU10")*ROW($B$1:$B$1000)),3) Note the ,3 at the end of the formula. HTH, Bernie MS Excel MVP "JeffP-" wrote in message ... I'm sorry to be so dense.... Here's a better rendition of my named range, however using your formula, I'm referencing the sheet name, 'Master Playoff Teams', Note I have many more rows so I padded out to 1k, and other columns that I'm not concerned with, however these are in fact the first three columns A, B, C. A B C row Standing Division Team 1 1 BU10 Edwards 2 2 BU10 Smith 3 3 BU10 Rojas 4 1 GU10 Rattigant 5 2 GU10 Markel 6 3 GU10 Ucamp This is my attempt to replicate the function you provided, however it results in a #Ref msg. =INDEX('Master Playoffs Teams'!$A$1:$H$1000,SUMPRODUCT(('Master Playoffs Teams'!$A$1:$A$1000=1)*('Master Playoffs Teams'!$B$1:$B$1000="BU10")*ROW($B$1:$B$1000))) Now, face to face with the second step like a chihuahua on the steps of the capital I'm looking straight ahead and all I see is a wall... "Bernie Deitrick" wrote: Jeff, =INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet Name'!$B$1:$B$100="BU10")*ROW($B$1:$B$100))) The BU10 can be in a cell and referenced in the formula - to make it easier to create a table: =INDEX('Sheet Name'!$C$1:$C$100,SUMPRODUCT(('Sheet Name'!$A$1:$A$100=1)*('Sheet Name'!$B$1:$B$100=A3)*ROW($B$1:$B$100))) HTH, Bernie MS Excel MVP "JeffP-" wrote in message ... Excel; from a named range table of teams by division (BU10, BU12, GU10...) and their standings, I want to place the value of a particular team name in a separate sheet w/in the same workbook, who is in a certain division (BU10) with a standing = 1, in essence query the named range and return a scalar value similar to an SQL query. Named Range 'MasterTeams' Standing Division Team ------------------------------------ 1 BU10 Edwards 2 BU10 Smith 3 BU10 Rojas 1 GU10 Rattigant 2 GU10 Markel 3 GU10 Ucamp I want on another sheet containing the playoff bracket to have in a cell the BU10 team name that has a standing of 1. SQL: select top 1 team from MasterGames where division = 'bu10' and standing = 1 TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Range reference via single Cell | Excel Discussion (Misc queries) | |||
Return Single Row of Numeric Data to Single Column | Excel Worksheet Functions | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions | |||
Identifying single column within named range | Excel Discussion (Misc queries) | |||
Single quotes in named range | Excel Worksheet Functions |