Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
I am making a basketball stats program.
I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
i2meek,
Go to the first cell that you did the paste-link in and edit the formula. Take the dollar signs out of the cell address in the formula. For example: change [filename]sheetname!$B$2 to [filename]sheetname!B2 But this doesn't sound like it is accomplishing what you want to accomplish. What you are describing here sounds like your links will bring in different players stats for the same game into cells where you want the same players stats for different games. My suggestion, Excel isn't the best tool for this. Access would be a lot better (if you have/know/can learn Access). In Access, you would have one table of games, one table of players, and one table of stats. In the stats table, you would look up the player from the player table and look up the game from the game table. Then you could have a form for Games and a form for Players. In the players form, you would look up the player you want to see, and it would list all of his stats by game. In the game form, you would look up the game you want to see, and it would list all of the stats by player. HTH, Conan "i2meek" wrote in message ... I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
In the formula below, I want the number 1 to then increase by one in the
column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
there are no dollar signs in my formula
"Conan Kelly" wrote: i2meek, Go to the first cell that you did the paste-link in and edit the formula. Take the dollar signs out of the cell address in the formula. For example: change [filename]sheetname!$B$2 to [filename]sheetname!B2 But this doesn't sound like it is accomplishing what you want to accomplish. What you are describing here sounds like your links will bring in different players stats for the same game into cells where you want the same players stats for different games. My suggestion, Excel isn't the best tool for this. Access would be a lot better (if you have/know/can learn Access). In Access, you would have one table of games, one table of players, and one table of stats. In the stats table, you would look up the player from the player table and look up the game from the game table. Then you could have a form for Games and a form for Players. In the players form, you would look up the player you want to see, and it would list all of his stats by game. In the game form, you would look up the game you want to see, and it would list all of the stats by player. HTH, Conan "i2meek" wrote in message ... I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
i2meek,
Excel doesn't work that way. It will change the "E4" to "E5" when copying/filling down. In order to do this you will need to use the INDIRECT() function (and maybe a helper column) I'm going to assume that row 1 is your lables and your data starts in row 2 I'm going to use column Z as a helper column (this helper column can be hidden so you don't see it). 1. In Z2 enter a "1" (w/o quotes) 2. In Z3 enter "=Z2+1" (w/o quotes) 3. Copy Z3 down until the value is "31" 4. In the cell that has your fomula "='[Data.xlsx]Game 1'!E4", change it to: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") Now if you do it this way, copying/filling down should get the results you are looking for, I THINK!!! (have not tested it) BUT, if you need to copy/fill accross, this formula will NOT change "E4" to "F4". It will take some more work to get it to do that. If you want to do this with out the helper column, it can be done, but I need more info from you. What cells are your lables? What cells do you want your data to be in? HTH, Conan "i2meek" wrote in message ... In the formula below, I want the number 1 to then increase by one in the column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
i2meek,
See my reply to your last post. Conan "i2meek" wrote in message ... there are no dollar signs in my formula "Conan Kelly" wrote: i2meek, Go to the first cell that you did the paste-link in and edit the formula. Take the dollar signs out of the cell address in the formula. For example: change [filename]sheetname!$B$2 to [filename]sheetname!B2 But this doesn't sound like it is accomplishing what you want to accomplish. What you are describing here sounds like your links will bring in different players stats for the same game into cells where you want the same players stats for different games. My suggestion, Excel isn't the best tool for this. Access would be a lot better (if you have/know/can learn Access). In Access, you would have one table of games, one table of players, and one table of stats. In the stats table, you would look up the player from the player table and look up the game from the game table. Then you could have a form for Games and a form for Players. In the players form, you would look up the player you want to see, and it would list all of his stats by game. In the game form, you would look up the game you want to see, and it would list all of the stats by player. HTH, Conan "i2meek" wrote in message ... I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
does the helper column go in the worksheet im paste-linking from or
paste-linking to? "Conan Kelly" wrote: i2meek, Excel doesn't work that way. It will change the "E4" to "E5" when copying/filling down. In order to do this you will need to use the INDIRECT() function (and maybe a helper column) I'm going to assume that row 1 is your lables and your data starts in row 2 I'm going to use column Z as a helper column (this helper column can be hidden so you don't see it). 1. In Z2 enter a "1" (w/o quotes) 2. In Z3 enter "=Z2+1" (w/o quotes) 3. Copy Z3 down until the value is "31" 4. In the cell that has your fomula "='[Data.xlsx]Game 1'!E4", change it to: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") Now if you do it this way, copying/filling down should get the results you are looking for, I THINK!!! (have not tested it) BUT, if you need to copy/fill accross, this formula will NOT change "E4" to "F4". It will take some more work to get it to do that. If you want to do this with out the helper column, it can be done, but I need more info from you. What cells are your lables? What cells do you want your data to be in? HTH, Conan "i2meek" wrote in message ... In the formula below, I want the number 1 to then increase by one in the column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
I got: #REF! in the cell -
It appears as though we are "manually" forcing the number to increase by 1, rather than having the cell paset-linked directly. I've noticed in the past that when I keyboard in a paste-link formula correctly it doesn't work, but when I paste-link it, it does. Why? Is there some setting I need to change? "Conan Kelly" wrote: i2meek, Excel doesn't work that way. It will change the "E4" to "E5" when copying/filling down. In order to do this you will need to use the INDIRECT() function (and maybe a helper column) I'm going to assume that row 1 is your lables and your data starts in row 2 I'm going to use column Z as a helper column (this helper column can be hidden so you don't see it). 1. In Z2 enter a "1" (w/o quotes) 2. In Z3 enter "=Z2+1" (w/o quotes) 3. Copy Z3 down until the value is "31" 4. In the cell that has your fomula "='[Data.xlsx]Game 1'!E4", change it to: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") Now if you do it this way, copying/filling down should get the results you are looking for, I THINK!!! (have not tested it) BUT, if you need to copy/fill accross, this formula will NOT change "E4" to "F4". It will take some more work to get it to do that. If you want to do this with out the helper column, it can be done, but I need more info from you. What cells are your lables? What cells do you want your data to be in? HTH, Conan "i2meek" wrote in message ... In the formula below, I want the number 1 to then increase by one in the column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
It goes in the sheet you are linking to.
"i2meek" wrote in message ... does the helper column go in the worksheet im paste-linking from or paste-linking to? "Conan Kelly" wrote: i2meek, Excel doesn't work that way. It will change the "E4" to "E5" when copying/filling down. In order to do this you will need to use the INDIRECT() function (and maybe a helper column) I'm going to assume that row 1 is your lables and your data starts in row 2 I'm going to use column Z as a helper column (this helper column can be hidden so you don't see it). 1. In Z2 enter a "1" (w/o quotes) 2. In Z3 enter "=Z2+1" (w/o quotes) 3. Copy Z3 down until the value is "31" 4. In the cell that has your fomula "='[Data.xlsx]Game 1'!E4", change it to: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") Now if you do it this way, copying/filling down should get the results you are looking for, I THINK!!! (have not tested it) BUT, if you need to copy/fill accross, this formula will NOT change "E4" to "F4". It will take some more work to get it to do that. If you want to do this with out the helper column, it can be done, but I need more info from you. What cells are your lables? What cells do you want your data to be in? HTH, Conan "i2meek" wrote in message ... In the formula below, I want the number 1 to then increase by one in the column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
Oooppss!!!
change the formula from: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") to: =INDIRECT("'[Data.xlsx]Game " & $Z2 & "'!E4") (take out the second "=") Sorry. See if that will work "i2meek" wrote in message ... I got: #REF! in the cell - It appears as though we are "manually" forcing the number to increase by 1, rather than having the cell paset-linked directly. I've noticed in the past that when I keyboard in a paste-link formula correctly it doesn't work, but when I paste-link it, it does. Why? Is there some setting I need to change? "Conan Kelly" wrote: i2meek, Excel doesn't work that way. It will change the "E4" to "E5" when copying/filling down. In order to do this you will need to use the INDIRECT() function (and maybe a helper column) I'm going to assume that row 1 is your lables and your data starts in row 2 I'm going to use column Z as a helper column (this helper column can be hidden so you don't see it). 1. In Z2 enter a "1" (w/o quotes) 2. In Z3 enter "=Z2+1" (w/o quotes) 3. Copy Z3 down until the value is "31" 4. In the cell that has your fomula "='[Data.xlsx]Game 1'!E4", change it to: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") Now if you do it this way, copying/filling down should get the results you are looking for, I THINK!!! (have not tested it) BUT, if you need to copy/fill accross, this formula will NOT change "E4" to "F4". It will take some more work to get it to do that. If you want to do this with out the helper column, it can be done, but I need more info from you. What cells are your lables? What cells do you want your data to be in? HTH, Conan "i2meek" wrote in message ... In the formula below, I want the number 1 to then increase by one in the column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
(By the way - i'm using excel 2007 and will later probably save as excel 97-03)
Worked in the cell I typed it in. I then selected some cells in the column, went to editing and chose fill down. It worked in the cell directly below, but the rest gave the #REF. ??? "Conan Kelly" wrote: Oooppss!!! change the formula from: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") to: =INDIRECT("'[Data.xlsx]Game " & $Z2 & "'!E4") (take out the second "=") Sorry. See if that will work "i2meek" wrote in message ... I got: #REF! in the cell - It appears as though we are "manually" forcing the number to increase by 1, rather than having the cell paset-linked directly. I've noticed in the past that when I keyboard in a paste-link formula correctly it doesn't work, but when I paste-link it, it does. Why? Is there some setting I need to change? "Conan Kelly" wrote: i2meek, Excel doesn't work that way. It will change the "E4" to "E5" when copying/filling down. In order to do this you will need to use the INDIRECT() function (and maybe a helper column) I'm going to assume that row 1 is your lables and your data starts in row 2 I'm going to use column Z as a helper column (this helper column can be hidden so you don't see it). 1. In Z2 enter a "1" (w/o quotes) 2. In Z3 enter "=Z2+1" (w/o quotes) 3. Copy Z3 down until the value is "31" 4. In the cell that has your fomula "='[Data.xlsx]Game 1'!E4", change it to: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") Now if you do it this way, copying/filling down should get the results you are looking for, I THINK!!! (have not tested it) BUT, if you need to copy/fill accross, this formula will NOT change "E4" to "F4". It will take some more work to get it to do that. If you want to do this with out the helper column, it can be done, but I need more info from you. What cells are your lables? What cells do you want your data to be in? HTH, Conan "i2meek" wrote in message ... In the formula below, I want the number 1 to then increase by one in the column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
In your helper column do your values go from 1 to 31 or do they go from 1 to
2? If they only go to "2", copy "2" down until the value is "31". Also, check the spelling of all of the sheet names in "Data.xlsx". This solution will only work if they are all named consistently: the word "Game", a space, and then the game number. For Example: Game 1 Game 2 Game 3 ... ... ... Game 29 Game 30 Game 31 FYI, I've never used XL 2007.....don't know anything about it. My answers are based on my knowledge of XL 2002-2003. My success at helping you through this depends on how compatible they are. "i2meek" wrote in message ... (By the way - i'm using excel 2007 and will later probably save as excel 97-03) Worked in the cell I typed it in. I then selected some cells in the column, went to editing and chose fill down. It worked in the cell directly below, but the rest gave the #REF. ??? "Conan Kelly" wrote: Oooppss!!! change the formula from: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") to: =INDIRECT("'[Data.xlsx]Game " & $Z2 & "'!E4") (take out the second "=") Sorry. See if that will work "i2meek" wrote in message ... I got: #REF! in the cell - It appears as though we are "manually" forcing the number to increase by 1, rather than having the cell paset-linked directly. I've noticed in the past that when I keyboard in a paste-link formula correctly it doesn't work, but when I paste-link it, it does. Why? Is there some setting I need to change? "Conan Kelly" wrote: i2meek, Excel doesn't work that way. It will change the "E4" to "E5" when copying/filling down. In order to do this you will need to use the INDIRECT() function (and maybe a helper column) I'm going to assume that row 1 is your lables and your data starts in row 2 I'm going to use column Z as a helper column (this helper column can be hidden so you don't see it). 1. In Z2 enter a "1" (w/o quotes) 2. In Z3 enter "=Z2+1" (w/o quotes) 3. Copy Z3 down until the value is "31" 4. In the cell that has your fomula "='[Data.xlsx]Game 1'!E4", change it to: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") Now if you do it this way, copying/filling down should get the results you are looking for, I THINK!!! (have not tested it) BUT, if you need to copy/fill accross, this formula will NOT change "E4" to "F4". It will take some more work to get it to do that. If you want to do this with out the helper column, it can be done, but I need more info from you. What cells are your lables? What cells do you want your data to be in? HTH, Conan "i2meek" wrote in message ... In the formula below, I want the number 1 to then increase by one in the column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
yes - my values go to 31 BUT i only named the 1st 2 worksheets the same and
just used #'s for the rest, planning to name them later - i'll give them the same name and see what happens, that's probably it - your good Thanks a million "Conan Kelly" wrote: In your helper column do your values go from 1 to 31 or do they go from 1 to 2? If they only go to "2", copy "2" down until the value is "31". Also, check the spelling of all of the sheet names in "Data.xlsx". This solution will only work if they are all named consistently: the word "Game", a space, and then the game number. For Example: Game 1 Game 2 Game 3 ... ... ... Game 29 Game 30 Game 31 FYI, I've never used XL 2007.....don't know anything about it. My answers are based on my knowledge of XL 2002-2003. My success at helping you through this depends on how compatible they are. "i2meek" wrote in message ... (By the way - i'm using excel 2007 and will later probably save as excel 97-03) Worked in the cell I typed it in. I then selected some cells in the column, went to editing and chose fill down. It worked in the cell directly below, but the rest gave the #REF. ??? "Conan Kelly" wrote: Oooppss!!! change the formula from: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") to: =INDIRECT("'[Data.xlsx]Game " & $Z2 & "'!E4") (take out the second "=") Sorry. See if that will work "i2meek" wrote in message ... I got: #REF! in the cell - It appears as though we are "manually" forcing the number to increase by 1, rather than having the cell paset-linked directly. I've noticed in the past that when I keyboard in a paste-link formula correctly it doesn't work, but when I paste-link it, it does. Why? Is there some setting I need to change? "Conan Kelly" wrote: i2meek, Excel doesn't work that way. It will change the "E4" to "E5" when copying/filling down. In order to do this you will need to use the INDIRECT() function (and maybe a helper column) I'm going to assume that row 1 is your lables and your data starts in row 2 I'm going to use column Z as a helper column (this helper column can be hidden so you don't see it). 1. In Z2 enter a "1" (w/o quotes) 2. In Z3 enter "=Z2+1" (w/o quotes) 3. Copy Z3 down until the value is "31" 4. In the cell that has your fomula "='[Data.xlsx]Game 1'!E4", change it to: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") Now if you do it this way, copying/filling down should get the results you are looking for, I THINK!!! (have not tested it) BUT, if you need to copy/fill accross, this formula will NOT change "E4" to "F4". It will take some more work to get it to do that. If you want to do this with out the helper column, it can be done, but I need more info from you. What cells are your lables? What cells do you want your data to be in? HTH, Conan "i2meek" wrote in message ... In the formula below, I want the number 1 to then increase by one in the column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
no problem......anytime!!!
"i2meek" wrote in message ... yes - my values go to 31 BUT i only named the 1st 2 worksheets the same and just used #'s for the rest, planning to name them later - i'll give them the same name and see what happens, that's probably it - your good Thanks a million "Conan Kelly" wrote: In your helper column do your values go from 1 to 31 or do they go from 1 to 2? If they only go to "2", copy "2" down until the value is "31". Also, check the spelling of all of the sheet names in "Data.xlsx". This solution will only work if they are all named consistently: the word "Game", a space, and then the game number. For Example: Game 1 Game 2 Game 3 ... ... ... Game 29 Game 30 Game 31 FYI, I've never used XL 2007.....don't know anything about it. My answers are based on my knowledge of XL 2002-2003. My success at helping you through this depends on how compatible they are. "i2meek" wrote in message ... (By the way - i'm using excel 2007 and will later probably save as excel 97-03) Worked in the cell I typed it in. I then selected some cells in the column, went to editing and chose fill down. It worked in the cell directly below, but the rest gave the #REF. ??? "Conan Kelly" wrote: Oooppss!!! change the formula from: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") to: =INDIRECT("'[Data.xlsx]Game " & $Z2 & "'!E4") (take out the second "=") Sorry. See if that will work "i2meek" wrote in message ... I got: #REF! in the cell - It appears as though we are "manually" forcing the number to increase by 1, rather than having the cell paset-linked directly. I've noticed in the past that when I keyboard in a paste-link formula correctly it doesn't work, but when I paste-link it, it does. Why? Is there some setting I need to change? "Conan Kelly" wrote: i2meek, Excel doesn't work that way. It will change the "E4" to "E5" when copying/filling down. In order to do this you will need to use the INDIRECT() function (and maybe a helper column) I'm going to assume that row 1 is your lables and your data starts in row 2 I'm going to use column Z as a helper column (this helper column can be hidden so you don't see it). 1. In Z2 enter a "1" (w/o quotes) 2. In Z3 enter "=Z2+1" (w/o quotes) 3. Copy Z3 down until the value is "31" 4. In the cell that has your fomula "='[Data.xlsx]Game 1'!E4", change it to: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") Now if you do it this way, copying/filling down should get the results you are looking for, I THINK!!! (have not tested it) BUT, if you need to copy/fill accross, this formula will NOT change "E4" to "F4". It will take some more work to get it to do that. If you want to do this with out the helper column, it can be done, but I need more info from you. What cells are your lables? What cells do you want your data to be in? HTH, Conan "i2meek" wrote in message ... In the formula below, I want the number 1 to then increase by one in the column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
well - i feel like i've gone brain dead - as you mentioned before, this
method will not allow me to fill right and go e4 to f4 to g4 ... but i need to do that - How can I? "Conan Kelly" wrote: no problem......anytime!!! "i2meek" wrote in message ... yes - my values go to 31 BUT i only named the 1st 2 worksheets the same and just used #'s for the rest, planning to name them later - i'll give them the same name and see what happens, that's probably it - your good Thanks a million "Conan Kelly" wrote: In your helper column do your values go from 1 to 31 or do they go from 1 to 2? If they only go to "2", copy "2" down until the value is "31". Also, check the spelling of all of the sheet names in "Data.xlsx". This solution will only work if they are all named consistently: the word "Game", a space, and then the game number. For Example: Game 1 Game 2 Game 3 ... ... ... Game 29 Game 30 Game 31 FYI, I've never used XL 2007.....don't know anything about it. My answers are based on my knowledge of XL 2002-2003. My success at helping you through this depends on how compatible they are. "i2meek" wrote in message ... (By the way - i'm using excel 2007 and will later probably save as excel 97-03) Worked in the cell I typed it in. I then selected some cells in the column, went to editing and chose fill down. It worked in the cell directly below, but the rest gave the #REF. ??? "Conan Kelly" wrote: Oooppss!!! change the formula from: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") to: =INDIRECT("'[Data.xlsx]Game " & $Z2 & "'!E4") (take out the second "=") Sorry. See if that will work "i2meek" wrote in message ... I got: #REF! in the cell - It appears as though we are "manually" forcing the number to increase by 1, rather than having the cell paset-linked directly. I've noticed in the past that when I keyboard in a paste-link formula correctly it doesn't work, but when I paste-link it, it does. Why? Is there some setting I need to change? "Conan Kelly" wrote: i2meek, Excel doesn't work that way. It will change the "E4" to "E5" when copying/filling down. In order to do this you will need to use the INDIRECT() function (and maybe a helper column) I'm going to assume that row 1 is your lables and your data starts in row 2 I'm going to use column Z as a helper column (this helper column can be hidden so you don't see it). 1. In Z2 enter a "1" (w/o quotes) 2. In Z3 enter "=Z2+1" (w/o quotes) 3. Copy Z3 down until the value is "31" 4. In the cell that has your fomula "='[Data.xlsx]Game 1'!E4", change it to: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") Now if you do it this way, copying/filling down should get the results you are looking for, I THINK!!! (have not tested it) BUT, if you need to copy/fill accross, this formula will NOT change "E4" to "F4". It will take some more work to get it to do that. If you want to do this with out the helper column, it can be done, but I need more info from you. What cells are your lables? What cells do you want your data to be in? HTH, Conan "i2meek" wrote in message ... In the formula below, I want the number 1 to then increase by one in the column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
pate-link & fill down?
i2meek,
Before I go on, there is something that I remembered about the INDIRECT() function on the way home last night. When using INDIRECT() you will need to have both the source file and the destination file open at the same time in order for INDIRECT() to pull info from the source. If you want these files to be a little more independent (i.e. you don't have to have the source file open to have the data show up in the destination file), then we will have to use actual links to the file. If this is a one-time set up process, we will have to switch gears (some what.......but kinda similar to what we have been doing) in order to get this to work right. If this is something that will be constantly changing (ie. new sheets will be added, new formulas will need to be entered, etc), then there is no good way to go about this. I can come up with a one-time set up process, but any formulas that need to be added will have to be done manually (they won't be able to be filled down/across and update automatically) If you will always have both files open at the same time, then we can continue with INDIRECT(). Now, in order to get the cell reference to change as you fill right, using the same method we used for filling down, you will need a "helper row" this time. In some row (once again, this can be hidden) you need to enter each letter in each cell. In this example, I'll use row 40 and I'll assume your data starts at B2, STAT labels are going across in row 1, and Game labels are going down in column A 1. In cell B40, enter the letter E. Enter "F" into C40. Continue this across row 40 entering the same number of letters (one per cell) for the same number of stats you are tracking. 2. Then you can edit your first formula (cell B2) to this: =INDIRECT("'[Data.xlsx]Game " & $Z2 & "'!" & B40 & "4") now using this method, it is assuming that for each game in the other file, the list of players will be the same in each sheet (same number of players in the exact same order). If a player gets traded (or a new one gets added) half way through the season, then the one that gets traded will still remain in the players list in all the games after being traded.......he just won't have any stats for those games. A player that gets added will need to be put at the bottom of the list of all of the games he plays in. HTH, Conan "i2meek" wrote in message ... well - i feel like i've gone brain dead - as you mentioned before, this method will not allow me to fill right and go e4 to f4 to g4 ... but i need to do that - How can I? "Conan Kelly" wrote: no problem......anytime!!! "i2meek" wrote in message ... yes - my values go to 31 BUT i only named the 1st 2 worksheets the same and just used #'s for the rest, planning to name them later - i'll give them the same name and see what happens, that's probably it - your good Thanks a million "Conan Kelly" wrote: In your helper column do your values go from 1 to 31 or do they go from 1 to 2? If they only go to "2", copy "2" down until the value is "31". Also, check the spelling of all of the sheet names in "Data.xlsx". This solution will only work if they are all named consistently: the word "Game", a space, and then the game number. For Example: Game 1 Game 2 Game 3 ... ... ... Game 29 Game 30 Game 31 FYI, I've never used XL 2007.....don't know anything about it. My answers are based on my knowledge of XL 2002-2003. My success at helping you through this depends on how compatible they are. "i2meek" wrote in message ... (By the way - i'm using excel 2007 and will later probably save as excel 97-03) Worked in the cell I typed it in. I then selected some cells in the column, went to editing and chose fill down. It worked in the cell directly below, but the rest gave the #REF. ??? "Conan Kelly" wrote: Oooppss!!! change the formula from: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") to: =INDIRECT("'[Data.xlsx]Game " & $Z2 & "'!E4") (take out the second "=") Sorry. See if that will work "i2meek" wrote in message ... I got: #REF! in the cell - It appears as though we are "manually" forcing the number to increase by 1, rather than having the cell paset-linked directly. I've noticed in the past that when I keyboard in a paste-link formula correctly it doesn't work, but when I paste-link it, it does. Why? Is there some setting I need to change? "Conan Kelly" wrote: i2meek, Excel doesn't work that way. It will change the "E4" to "E5" when copying/filling down. In order to do this you will need to use the INDIRECT() function (and maybe a helper column) I'm going to assume that row 1 is your lables and your data starts in row 2 I'm going to use column Z as a helper column (this helper column can be hidden so you don't see it). 1. In Z2 enter a "1" (w/o quotes) 2. In Z3 enter "=Z2+1" (w/o quotes) 3. Copy Z3 down until the value is "31" 4. In the cell that has your fomula "='[Data.xlsx]Game 1'!E4", change it to: =INDIRECT("='[Data.xlsx]Game " & $Z2 & "'!E4") Now if you do it this way, copying/filling down should get the results you are looking for, I THINK!!! (have not tested it) BUT, if you need to copy/fill accross, this formula will NOT change "E4" to "F4". It will take some more work to get it to do that. If you want to do this with out the helper column, it can be done, but I need more info from you. What cells are your lables? What cells do you want your data to be in? HTH, Conan "i2meek" wrote in message ... In the formula below, I want the number 1 to then increase by one in the column directly below, all the way to number 31 Player 1 stat in first row: ='[Data.xlsx]Game 1'!E4 last entry in column: ='[Data.xlsx]Game31'!E4 "i2meek" wrote: I am making a basketball stats program. I have a workbook where data is entered. In this workbook there are 31 worksheets, one for data to be entered for each game. The 31 worksheets have all 23 players' names down 2 columns on the left, and all the stat headings across the top row. I have another workbook that displays player stats with calculations and has 23 worksheets, one for each player. It has the 31 games listed in a column on the left and the stat headings across the top. I would like to link paste-link the data in worksheet1, row1 to the player 1 stat row1. The data in worksheet 1 row 2 to the player2 stat row1, Worksheet1 row3 to player3 row1, etc. Then after game 2, paste-link the data in worksheet2 row1 to player1 row2, worksheet2 row2 to player2 row2, worksheet2 row3, to player3 row2 etc. I thought I could do the first paste-link and then fill down. However, when I do this, the cell location does not increase by 1. What is the quickest way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Some formulas don't track copies, pastes, fill right, fill down | Excel Discussion (Misc queries) | |||
fill list manually and link to colour buttons in overview | Excel Discussion (Misc queries) | |||
Fill in form to type Item descrictions and costs and fill in funct | Excel Worksheet Functions | |||
Link and fill cells in workbooks | Excel Worksheet Functions | |||
I have a list of data, fill in the gaps. FILL function won't work | Excel Discussion (Misc queries) |