linking ranges of cells

  • Thread starter Fran McConville
  • Start date
F

Fran McConville

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
 
M

Max

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
 
M

Max

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
 
F

Fran McConville

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
 
D

Dave Peterson

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

Max

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

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!!
Click to expand...

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
 
F

Fran McConville

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
 
M

Max

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)
 
F

Fran McConville

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
 
M

Max

Glaaad to hear you got it sorted out said:
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?
Click to expand...

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
 
Top