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,....

It still doesn't work. Yes, both files are open and =[File1]Wks1!$E$50 works.
I have got 3 formulae from you so far:
=indirect("[File1]Wks1!R50C"&(row()-4),false)
=indirect("[File1]Wks1!$E$50")
=indirect("[File1]Wks1!r50C5,false)
I have tried all three one by one and none works. In the third formula,
there is no closing inverted commas, I assumed =indirect("[Alcoa Sales
Tracker.xls]Wks1!r50C5",false) but that doesn't work either


"bj" wrote:

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,....

That worked, thanks. Can you explain the uses and syntax of the index function

"Ragdyer" wrote:

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,....

That would take a small book since it's a multifaceted function with more
then one form.
And even how it's used here is rather simplistic, since it's only
referencing a single row.

How about you read the Help files on it, and then post back with any
specific questions.

In this context, you reference an array, and then return the contents of the
cell within that array, by identifying it's location *within* the array.

When the array is a single row or a single column, you'll only need a single
location indicator.

=INDEX(50:50,5)
Entered in the Wks1 WS, would return the contents of the cell in the fifth
column in the 50th row, namely ... E50.

=INDEX(F50:P50,5)
Would return the contents of J50 ... the *fifth* column of the *array* -
F50 to P50.

Now, with the intention to copy *down*, and *automatically* increment the
cell location indicator, we replace the "5" with something that will
increment itself, namely the ROW() function.

Wherever we wish to *start* the return from, that's the row number we enter
into the primary formula:

=INDEX($F$50:$P$50,ROW(5:5))

This works exactly the same way if we're looking to reference columns, where
we might be copying *not down* a column, BUT *across* columns, *along* a
row:

=INDEX(E:E,5)

Will return the contents of E5.


=INDEX(E40:E60,5)

Will return the contents of E44


To copy along any row, to return the contents of the array E40 to E60,
starting at E40:

=INDEX($E$40:$E$60,COLUMN(A:A))

To copy along any row, to return the contents of Column E, starting at E10:

=INDEX($E:$E,COLUMN(J:J))

I hope this gives you a little better understanding of how INDEX() is used
in your context here.


Just a note ... you could also use it to reference rows *and* columns.

=INDEX(A1:E50,5,4)

Which returns the contents of D5

And is a *good substitute* for the OFFSET() function, since *unlike Offset*,
it works on *closed* WBs and it's *not* volatile.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"neeraj" wrote in message
...
That worked, thanks. Can you explain the uses and syntax of the index
function

"Ragdyer" wrote:

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,....