Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 ---- |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
"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 ---- |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking Pictures in Cells | Excel Worksheet Functions | |||
Linking cells in a worksheet to other worksheets in a workbook | Excel Discussion (Misc queries) | |||
Unlocking Cells when a worksheet is protected... | Excel Discussion (Misc queries) | |||
Help me! There is problem with cells view... | Excel Discussion (Misc queries) | |||
linking cells | Excel Worksheet Functions |