I am trying to use fill handle to copy a formula down a column from a row in
another workbook. The column is B9,B10,B11, .... and the row in a worksheet
say Wks1 of another file, say File 1 is E50, F50, G50,... The formula in B9
looked like =[File1]Wks1!$E$50. I use the fill handle to copy the formula
down the column in B10, B11 but what I got in all cells that is B10, B11 was
the same that is =[File1]Wks1!$E$50. I would have like to fill to
=[File1]Wks1!F50, =[File1]Wks1!G50. I then changed the cell reference from
the fixed cell reference in B9 to =[File1]Wks1!E$50. When the move the fill
handle down in my destination column, it doesn't change the cell reference
and it remains E$50. I have tried changing the formula in B9 to both $E50 and
to E50 but it changes it to E51, E52. How can I fill my column such that the
cells B10, B11,....derive their values from F50, G50,....

You can use the indirect() function
=indirect("[File1]Wks1!R50C"&(row()-4),false)
should work

"neeraj" wrote:

I am trying to use fill handle to copy a formula down a column from a row in
another workbook. The column is B9,B10,B11, .... and the row in a worksheet
say Wks1 of another file, say File 1 is E50, F50, G50,... The formula in B9
looked like =[File1]Wks1!$E$50. I use the fill handle to copy the formula
down the column in B10, B11 but what I got in all cells that is B10, B11 was
the same that is =[File1]Wks1!$E$50. I would have like to fill to
=[File1]Wks1!F50, =[File1]Wks1!G50. I then changed the cell reference from
the fixed cell reference in B9 to =[File1]Wks1!E$50. When the move the fill
handle down in my destination column, it doesn't change the cell reference
and it remains E$50. I have tried changing the formula in B9 to both $E50 and
to E50 but it changes it to E51, E52. How can I fill my column such that the
cells B10, B11,....derive their values from F50, G50,....

I am not familiar to Indirect Function nor I could understand it fully from
the Help menu. Can you explain the syntax of this function: What do R50C,
row()-4 and false signify. Thanks.

"bj" wrote:

You can use the indirect() function
=indirect("[File1]Wks1!R50C"&(row()-4),false)
should work

"neeraj" wrote:

I am trying to use fill handle to copy a formula down a column from a row in
another workbook. The column is B9,B10,B11, .... and the row in a worksheet
say Wks1 of another file, say File 1 is E50, F50, G50,... The formula in B9
looked like =[File1]Wks1!$E$50. I use the fill handle to copy the formula
down the column in B10, B11 but what I got in all cells that is B10, B11 was
the same that is =[File1]Wks1!$E$50. I would have like to fill to
=[File1]Wks1!F50, =[File1]Wks1!G50. I then changed the cell reference from
the fixed cell reference in B9 to =[File1]Wks1!E$50. When the move the fill
handle down in my destination column, it doesn't change the cell reference
and it remains E$50. I have tried changing the formula in B9 to both $E50 and
to E50 but it changes it to E51, E52. How can I fill my column such that the
cells B10, B11,....derive their values from F50, G50,....

the "R50 means row 50 and the C"&Row()-4
indicates that it is the column number in the reference equal to 4 less than
the row number of he equation
(Column E is equivelent to Column 5, Column F to Column 6 etc.

"neeraj" wrote:

I am not familiar to Indirect Function nor I could understand it fully from
the Help menu. Can you explain the syntax of this function: What do R50C,
row()-4 and false signify. Thanks.

"bj" wrote:

You can use the indirect() function
=indirect("[File1]Wks1!R50C"&(row()-4),false)
should work

"neeraj" wrote:

I am trying to use fill handle to copy a formula down a column from a row in
another workbook. The column is B9,B10,B11, .... and the row in a worksheet
say Wks1 of another file, say File 1 is E50, F50, G50,... The formula in B9
looked like =[File1]Wks1!$E$50. I use the fill handle to copy the formula
down the column in B10, B11 but what I got in all cells that is B10, B11 was
the same that is =[File1]Wks1!$E$50. I would have like to fill to
=[File1]Wks1!F50, =[File1]Wks1!G50. I then changed the cell reference from
the fixed cell reference in B9 to =[File1]Wks1!E$50. When the move the fill
handle down in my destination column, it doesn't change the cell reference
and it remains E$50. I have tried changing the formula in B9 to both $E50 and
to E50 but it changes it to E51, E52. How can I fill my column such that the
cells B10, B11,....derive their values from F50, G50,....

I copied the formula that you gave to me
=indirect("[File1]Wks1!R50C"&(row()-4),false)
into the B9 cell and on entering it gave a REF! error. Both the source and
destination files were open( they are posted on a common/network drive)(I
understand from Help that both have to be open otherwise REF! error) Just
wanted to make sure with you if the location of " " is exactly where you
intended to be or if there is any other error.
Is there an alternate way to do this (especially in the light of the fact
that the source file has to be open if using Indirect function and this
having to be used by inexperienced users on a network might cause
errors)...alternate way that I was thinking of was Transpose : row values
have to be copied into a column of another file. I tried it: It did not work
for me, I have not used it before. If u think it can, can you give the syntax
particularly for my problem with the exact cell references. Thanks.
"bj" wrote:

the "R50 means row 50 and the C"&Row()-4
indicates that it is the column number in the reference equal to 4 less than
the row number of he equation
(Column E is equivelent to Column 5, Column F to Column 6 etc.

"neeraj" wrote:

I am not familiar to Indirect Function nor I could understand it fully from
the Help menu. Can you explain the syntax of this function: What do R50C,
row()-4 and false signify. Thanks.

"bj" wrote:

You can use the indirect() function
=indirect("[File1]Wks1!R50C"&(row()-4),false)
should work

"neeraj" wrote:

I am trying to use fill handle to copy a formula down a column from a row in
another workbook. The column is B9,B10,B11, .... and the row in a worksheet
say Wks1 of another file, say File 1 is E50, F50, G50,... The formula in B9
looked like =[File1]Wks1!$E$50. I use the fill handle to copy the formula
down the column in B10, B11 but what I got in all cells that is B10, B11 was
the same that is =[File1]Wks1!$E$50. I would have like to fill to
=[File1]Wks1!F50, =[File1]Wks1!G50. I then changed the cell reference from
the fixed cell reference in B9 to =[File1]Wks1!E$50. When the move the fill
handle down in my destination column, it doesn't change the cell reference
and it remains E$50. I have tried changing the formula in B9 to both $E50 and
to E50 but it changes it to E51, E52. How can I fill my column such that the
cells B10, B11,....derive their values from F50, G50,....

i'm not sure why you got the ref error.
in your main file just
enter
=indirect("[File1]Wks1!$E$50")
and
=indirect("[File1]Wks1!r50C5,false)
they should both work if both the files are open and
if =[File1]Wks1!$E$50 works


"neeraj" wrote:

I copied the formula that you gave to me
=indirect("[File1]Wks1!R50C"&(row()-4),false)
into the B9 cell and on entering it gave a REF! error. Both the source and
destination files were open( they are posted on a common/network drive)(I
understand from Help that both have to be open otherwise REF! error) Just
wanted to make sure with you if the location of " " is exactly where you
intended to be or if there is any other error.
Is there an alternate way to do this (especially in the light of the fact
that the source file has to be open if using Indirect function and this
having to be used by inexperienced users on a network might cause
errors)...alternate way that I was thinking of was Transpose : row values
have to be copied into a column of another file. I tried it: It did not work
for me, I have not used it before. If u think it can, can you give the syntax
particularly for my problem with the exact cell references. Thanks.
"bj" wrote:

the "R50 means row 50 and the C"&Row()-4
indicates that it is the column number in the reference equal to 4 less than
the row number of he equation
(Column E is equivelent to Column 5, Column F to Column 6 etc.

"neeraj" wrote:

I am not familiar to Indirect Function nor I could understand it fully from
the Help menu. Can you explain the syntax of this function: What do R50C,
row()-4 and false signify. Thanks.

"bj" wrote:

You can use the indirect() function
=indirect("[File1]Wks1!R50C"&(row()-4),false)
should work

"neeraj" wrote:

I am trying to use fill handle to copy a formula down a column from a row in
another workbook. The column is B9,B10,B11, .... and the row in a worksheet
say Wks1 of another file, say File 1 is E50, F50, G50,... The formula in B9
looked like =[File1]Wks1!$E$50. I use the fill handle to copy the formula
down the column in B10, B11 but what I got in all cells that is B10, B11 was
the same that is =[File1]Wks1!$E$50. I would have like to fill to
=[File1]Wks1!F50, =[File1]Wks1!G50. I then changed the cell reference from
the fixed cell reference in B9 to =[File1]Wks1!E$50. When the move the fill
handle down in my destination column, it doesn't change the cell reference
and it remains E$50. I have tried changing the formula in B9 to both $E50 and
to E50 but it changes it to E51, E52. How can I fill my column such that the
cells B10, B11,....derive their values from F50, G50,....

Use of the Index() function will answer your concerns about being able to
access the data from *closed* WBs.

Enter this anywhere you wish, and copy down as needed:

=INDEX('[File 1.xls]Wks1'!$50:$50,ROW(5:5))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"neeraj" wrote in message
...
I copied the formula that you gave to me
=indirect("[File1]Wks1!R50C"&(row()-4),false)
into the B9 cell and on entering it gave a REF! error. Both the source and
destination files were open( they are posted on a common/network drive)(I
understand from Help that both have to be open otherwise REF! error) Just
wanted to make sure with you if the location of " " is exactly where you
intended to be or if there is any other error.
Is there an alternate way to do this (especially in the light of the fact
that the source file has to be open if using Indirect function and this
having to be used by inexperienced users on a network might cause
errors)...alternate way that I was thinking of was Transpose : row values
have to be copied into a column of another file. I tried it: It did not
work
for me, I have not used it before. If u think it can, can you give the
syntax
particularly for my problem with the exact cell references. Thanks.
"bj" wrote:

the "R50 means row 50 and the C"&Row()-4
indicates that it is the column number in the reference equal to 4 less
than
the row number of he equation
(Column E is equivelent to Column 5, Column F to Column 6 etc.

"neeraj" wrote:

I am not familiar to Indirect Function nor I could understand it fully
from
the Help menu. Can you explain the syntax of this function: What do
R50C,
row()-4 and false signify. Thanks.

"bj" wrote:

You can use the indirect() function
=indirect("[File1]Wks1!R50C"&(row()-4),false)
should work

"neeraj" wrote:

I am trying to use fill handle to copy a formula down a column from
a row in
another workbook. The column is B9,B10,B11, .... and the row in a
worksheet
say Wks1 of another file, say File 1 is E50, F50, G50,... The
formula in B9
looked like =[File1]Wks1!$E$50. I use the fill handle to copy the
formula
down the column in B10, B11 but what I got in all cells that is
B10, B11 was
the same that is =[File1]Wks1!$E$50. I would have like to fill to
=[File1]Wks1!F50, =[File1]Wks1!G50. I then changed the cell
reference from
the fixed cell reference in B9 to =[File1]Wks1!E$50. When the move
the fill
handle down in my destination column, it doesn't change the cell
reference
and it remains E$50. I have tried changing the formula in B9 to
both $E50 and
to E50 but it changes it to E51, E52. How can I fill my column such
that the
cells B10, B11,....derive their values from F50, G50,....