linking ranges of cells
 

Hi!

How can I link all of the cells in a row to all of the cells in a row in
another worksheet without having to individually link each cell?

Thanks!

-Fran

On the face of it, think we can just copy across from the starting cell's
link formula

Assume we want to link Sheet1's row1 to Sheet2's row1

In Sheet2,

Put in A1: =Sheet1!A1
Copy A1 across to IV1

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Fran McConville" wrote in
message ...
Hi!

How can I link all of the cells in a row to all of the cells in a row in
another worksheet without having to individually link each cell?

Thanks!

-Fran

And for a cleaner look in Sheet2, we could also suppress the display of
extraneous zeros via: Tools Options View tab Uncheck "Zero values"
OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Oh...if only it were that simple Max! That certainly works within a
worsheet, and within a workbook from one sheet to another, but it doesn't
work from one workbook to another. Don't ask me why!!

-Fran

When you use another workbook, the cell uses absolute references.

You see $a$1 instead of just a1. After you finish with the first cell, change
that reference to relative reference ($a$1 to a1) and then drag across.



Fran McConville wrote:

Oh...if only it were that simple Max! That certainly works within a
worsheet, and within a workbook from one sheet to another, but it doesn't
work from one workbook to another. Don't ask me why!!

-Fran

--

Dave Peterson

"Fran McConville" wrote
Oh...if only it were that simple Max! That certainly works within a
worksheet, and within a workbook from one sheet to another,

But on the face of it, doesn't that cover what was described in your
original question ?

but it doesn't work from one workbook to another. Don't ask me why!!

If the source Sheet1 were in another book, say Book4.xls, then with
Book4.xls open, the formula in Sheet2's A1 would look something like

: =[Book4]Sheet1!A1

And we could just copy across to link the entire row in the same way
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks for the reply, Max, but I'm afraid it just isn't working for me
between workbooks. When I copy cell A1 and past it to a few other cells in
the same row, I get the identical link (=[Book4]Sheet1!A1) in every cell.
Within a workbook it works as you describe.

Please try it, and I hope you'll see what I'm talking about. Perhaps I have
a glitch in my software?

-Fran

Yes, I just retested it again once more time. It works ok for me here
whether I copy across from Sheet2's A1 by dragging the fill handle, or by
copying A1, then pasting into B1:E1 (say). The formula adjusts relatively.

Does Sheet2's A1 return a correct test value from Sheet1's A1 in Book4.xls
when you press enter after putting the formula there ? (Book4.xls is assumed
open)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Fran McConville" wrote in
message ...
Thanks for the reply, Max, but I'm afraid it just isn't working for me
between workbooks. When I copy cell A1 and past it to a few other cells in
the same row, I get the identical link (=[Book4]Sheet1!A1) in every cell.
Within a workbook it works as you describe.

Please try it, and I hope you'll see what I'm talking about. Perhaps I
have
a glitch in my software?

-Fran

Ah ha!

Max, you suggested I take a closer look at the formula in cell A1 -
I noticed that the formula looked like this: =[Book4]Sheet1!$A$1
For some reason Excel put the $'s in there on its own. My problem is solved.

On a related note, do you know of a shortcut to make a non-relative link to
a cell without manually typing in the $'s?

Thanks again,

-Fran

Glaaad to hear you got it sorted out <g !

On a related note, do you know of a shortcut to make a non-relative link
to
a cell without manually typing in the $'s?

Think Dave Peterson had earlier posted Excel uses absolute references for
the linked cell if it's to / in another book

But we could use the F4 key to toggle through the 4 cell reference states
instead of amending manually the link cell formula after we've clicked to
produce the link and entered the formula

=[Book4]Sheet1!$A$1

Try this:
Select the part: $A$1 in the formula
Press F4 key to toggle through the 4 cell ref states: $A$1, A$1, $A1, A1
and stop of course, at the desired state
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----