Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Function that references cells to other cells...
I know the title is a bit confusing, and the concept may be hard to
explain, but if I could do this it would save me hours, maybe even days of time. I would appreciate the help. I'm making a spreadsheet that takes a basketball player's statistics in real life, and turns them into a straight number using a formula I developed. (It is for a fantasy basketball league I run, where the players score different amount of points in our league depending on what offense they run. But you probably don't need to know that.) For example, if the team decides to run a Fast Break offense, the formula I have developed reads like so: (POINTS * 1.5) + (REBOUNDS *0.5) + (ASSISTS*3) + BLOCKS + STEALS - (TURNOVERS *2). So, at the top of my spreadsheet I have a table with all the players on a certain NBA team, and a table that just holds values I will input. The reason the players are grouped by NBA team is that when I read a box score to input the players real life statistics, it's easier to do it team by team than to switch between NBA teams and go player by player. (If you need clarification on this, just ask, but it doesn't really matter that much I don't think.) So the top of the spreadsheet looks like so: [image: http://mywebpage.netscape.com/FFSBas...TablesBig.jpg] Which zoomed in is: [image: http://mywebpage.netscape.com/FFSBas...ablesZoom.jpg] Then after every team is set up with a table like that, I then have the rosters of the teams in my league set up at the bottom, to reference to the values entered at the top. Here is a picture of how that looks: [image: http://mywebpage.netscape.com/FFSBas...planation.jpg] Here are the key points in this picture. First, the red wording was put in by me as explanation. At the top left as you can see is the name of the team in my league. The column with "PG, SG, SF, etc.", is headed by "Position", because that is the position on the court that the player is playing on his team. (You can ignore this, it does have to do with what I'm doing, but once I figure out how to do it, I can apply it to different positions.) The next column has the players' name. And this next column is the column that really matters. In this column I put in the row that that players statistics are located on at the top of the sheet. If you look back at the picture of the NBA Teams' roster, you will see that each player is located on a certain row (obviously, that's how Excel works). So for example, Gilbert Arenas' actual statistics, when I type in the box score at the top of the sheet, are in row 119 of the spreadsheet. The next columns are just what offense they run, and they contain the following formula. However this formula changes depending on what offense and what position the player plays. Here is what is in the Superstar offense for Gilbert Arenas: [image: http://mywebpage.netscape.com/FFSBas...laExample.jpg] Now in this example, Arenas has the number of points he scored in his NBA game divided by 2. His NBA points will be located in cell C119 once I input the box scores. The next numbers are how his rebounds, assists, blocks, steals and turnovers will be modified. All point guards running the shootout offense have this same thing. And here is my problem, and what I want to do. Instead of having to type that player's unique row number into every single cell, is there a way to reference to one cell depending on another cell's value. Let me elaborate. Is there a way to make Gilbert Arenas' formula such that instead of typing in 119 for all of Arenas' formulas, I could say I just want it to reference to cell D496 (which is where Arenas' row number is contained on the spreadsheet) take that value and then use that to determine the row number. See this is where the wording is hard. What I want to do is, instead of cell C119 I want it to say cell C(=D496). When it reads cell D496, it would find the value 119 there. Then the value inside the parentheses would be 119, and Excel could figure out that it wants to find C119. What I'm doing is creating my own function using the Visual Basic editor, so I could say =PGShootout(D496). Then the function I created would contain the formula I needed, accepting the argument "RowNumber". Arenas' row number is contained in D496, as I said before, PGShootout would read D496, find that it's value is 119, then in the function PGShootout I could say "Multiply Column C, Row (Row Number) by 0.5" (of course in programming language, though). So it would look for RowNumber, which in the Arenas case is D496, whose value is 119, and figure out that it needs to multiply column C, Row 119 by 0.5. I tried to explain myself as best as possible here, but it still might be unclear to some. If you have any questions, please ask. I could REALLY use the help, and I unfortunately have a deadline coming up very soon on this, as October 28th is the start of the NBA season. Thanks in advance. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Function that references cells to other cells...
I didn't look at your picture, but assume in the top of the sheet, the
players name is in column B and points in C in the place where you have the formulas, assume the player's name is in column A. =Vlookup(A496,$B$1:$G$250,2,False) should give you the points for the player listed in cell A496 from the table. the 2 says t get it from the second column in the top table (column C). -- Regards, Tom Ogilvy jayhawk1919 wrote in message ... I know the title is a bit confusing, and the concept may be hard to explain, but if I could do this it would save me hours, maybe even days of time. I would appreciate the help. I'm making a spreadsheet that takes a basketball player's statistics in real life, and turns them into a straight number using a formula I developed. (It is for a fantasy basketball league I run, where the players score different amount of points in our league depending on what offense they run. But you probably don't need to know that.) For example, if the team decides to run a Fast Break offense, the formula I have developed reads like so: (POINTS * 1.5) + (REBOUNDS *0.5) + (ASSISTS*3) + BLOCKS + STEALS - (TURNOVERS *2). So, at the top of my spreadsheet I have a table with all the players on a certain NBA team, and a table that just holds values I will input. The reason the players are grouped by NBA team is that when I read a box score to input the players real life statistics, it's easier to do it team by team than to switch between NBA teams and go player by player. (If you need clarification on this, just ask, but it doesn't really matter that much I don't think.) So the top of the spreadsheet looks like so: [image: http://mywebpage.netscape.com/FFSBas...TablesBig.jpg] Which zoomed in is: [image: http://mywebpage.netscape.com/FFSBas...ablesZoom.jpg] Then after every team is set up with a table like that, I then have the rosters of the teams in my league set up at the bottom, to reference to the values entered at the top. Here is a picture of how that looks: [image: http://mywebpage.netscape.com/FFSBas...planation.jpg] Here are the key points in this picture. First, the red wording was put in by me as explanation. At the top left as you can see is the name of the team in my league. The column with "PG, SG, SF, etc.", is headed by "Position", because that is the position on the court that the player is playing on his team. (You can ignore this, it does have to do with what I'm doing, but once I figure out how to do it, I can apply it to different positions.) The next column has the players' name. And this next column is the column that really matters. In this column I put in the row that that players statistics are located on at the top of the sheet. If you look back at the picture of the NBA Teams' roster, you will see that each player is located on a certain row (obviously, that's how Excel works). So for example, Gilbert Arenas' actual statistics, when I type in the box score at the top of the sheet, are in row 119 of the spreadsheet. The next columns are just what offense they run, and they contain the following formula. However this formula changes depending on what offense and what position the player plays. Here is what is in the Superstar offense for Gilbert Arenas: [image: http://mywebpage.netscape.com/FFSBas...laExample.jpg] Now in this example, Arenas has the number of points he scored in his NBA game divided by 2. His NBA points will be located in cell C119 once I input the box scores. The next numbers are how his rebounds, assists, blocks, steals and turnovers will be modified. All point guards running the shootout offense have this same thing. And here is my problem, and what I want to do. Instead of having to type that player's unique row number into every single cell, is there a way to reference to one cell depending on another cell's value. Let me elaborate. Is there a way to make Gilbert Arenas' formula such that instead of typing in 119 for all of Arenas' formulas, I could say I just want it to reference to cell D496 (which is where Arenas' row number is contained on the spreadsheet) take that value and then use that to determine the row number. See this is where the wording is hard. What I want to do is, instead of cell C119 I want it to say cell C(=D496). When it reads cell D496, it would find the value 119 there. Then the value inside the parentheses would be 119, and Excel could figure out that it wants to find C119. What I'm doing is creating my own function using the Visual Basic editor, so I could say =PGShootout(D496). Then the function I created would contain the formula I needed, accepting the argument "RowNumber". Arenas' row number is contained in D496, as I said before, PGShootout would read D496, find that it's value is 119, then in the function PGShootout I could say "Multiply Column C, Row (Row Number) by 0.5" (of course in programming language, though). So it would look for RowNumber, which in the Arenas case is D496, whose value is 119, and figure out that it needs to multiply column C, Row 119 by 0.5. I tried to explain myself as best as possible here, but it still might be unclear to some. If you have any questions, please ask. I could REALLY use the help, and I unfortunately have a deadline coming up very soon on this, as October 28th is the start of the NBA season. Thanks in advance. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Function that references cells to other cells...
So you're saying I should look it up by player name rather than row
number? And I'm not quite sure I understand what I'm supposed to do according to what you said. The "table" I referred to is not an actual table, just cells that have borders drawn around them so they appear as a table. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Function that references cells to other cells...
=Offset($A$1,D496-1,2)*0.5+Offset($A$1,D496-1,3)+Offset($A$1,D496-1,4)*2+Off
set($A$1,D496-1,5)+Offset($A$1,D496-1,6)-Offset($A$1,D496,7) or =Indirect("C"&D496)*0.5+Indirect("D"&D496)+Indirec t("E"&D496)*2+Indirect("F" &D496)+Indirect("G"&D496)-Indirect("H"&D496) are two ways to do your formula. In D496 you could have =Match(C496,$A$1:$A$250,0) Change 250 to reflect the last row of the team tables. This should return 119 and save you having to look it up. -- Regards, Tom Ogilvy jayhawk1919 wrote in message ... So you're saying I should look it up by player name rather than row number? And I'm not quite sure I understand what I'm supposed to do according to what you said. The "table" I referred to is not an actual table, just cells that have borders drawn around them so they appear as a table. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Function that references cells to other cells...
The
"=Indirect("C"&D496)*0.5+Indirect("D"&D496)+Indire ct("E"&D496)*2+Indirect("F" &D496)+Indirect("G"&D496)-Indirect("H"&D496)" formula is easy enough to understand. Thanks a bunch for your help, I have one more question though. Does this formula require the "=Match(C496,$A$1:$A$250,0)" to be in D496? If so, could you explain it so I understand why I need it? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Function that references cells to other cells...
Nevermind, that Indirect thing did the trick. Thank you so much! You
don't know how much time you just saved me, thanks so much! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Function that references cells to other cells...
No it doesn't. It requires D496 to display the value 119 (or the
appropriate row number) - it doesn't care how that is produced - hard coded or produced by a formula. -- Regards, Tom Ogilvy jayhawk1919 wrote in message ... The "=Indirect("C"&D496)*0.5+Indirect("D"&D496)+Indire ct("E"&D496)*2+Indirect("F " &D496)+Indirect("G"&D496)-Indirect("H"&D496)" formula is easy enough to understand. Thanks a bunch for your help, I have one more question though. Does this formula require the "=Match(C496,$A$1:$A$250,0)" to be in D496? If so, could you explain it so I understand why I need it? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Function to Reference Cells that Contain Certain Values | Excel Worksheet Functions | |||
references to named cells | Excel Discussion (Misc queries) | |||
Can references (to cells being sorted) move with the cells? | Setting up and Configuration of Excel | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
working with references of cells | Excel Worksheet Functions |