pate-link & fill down?

[i2meek]

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?

 

[Conan Kelly]

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]

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]

there are no dollar signs in my formula

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

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?

 

[Conan Kelly]

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

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

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?

 

[Conan Kelly]

i2meek,

See my reply to your last post.

Conan

there are no dollar signs in my formula

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

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?

 

[i2meek]

does the helper column go in the worksheet im paste-linking from or
paste-linking to?

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

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

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?

 

[i2meek]

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?

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

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

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?

 

[Conan Kelly]

It goes in the sheet you are linking to.

does the helper column go in the worksheet im paste-linking from or
paste-linking to?

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

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


:

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?

 

[Conan Kelly]

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

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?

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

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


:

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?

 

[i2meek]

(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. ???

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

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?

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




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


:

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?

 

[Conan Kelly]

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.

(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. ???

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

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?

:

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




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


:

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?

 

[i2meek]

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

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.

(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. ???

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



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?

:

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




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


:

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?

 

[Conan Kelly]

no problem......anytime!!!

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

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.

(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. ???

:

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



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?

:

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




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


:

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?

 

[i2meek]

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?

no problem......anytime!!!

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

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.





(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. ???

:

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



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?

:

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




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


:

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?

 

[Conan Kelly]

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

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?

no problem......anytime!!!

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

:

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.





(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. ???

:

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



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?

:

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




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


:

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?