New User - Linking cells on different worksheets

[Boz]

Hi All

What is the best way to link cells on different worksheets?

Many thanx

Boz

[HiArt]

Hi Boz,

=sheetname!celladdress

for example =Sheet1!A1

Dont forget you can use the $ symbol to stop an address changing when you copy it,

=Sheet1!$A$1 will never change the cell pointer if you copy it within the target sheet. Ply with this a little and you'll understand.

In sheet1 put some values in a1, a2 and a3. In sheet 2 in cell A! put = Sheet1!A1 and the value from cell A1 in Sheet 1 should appear.

Now in sheet 2 copy cell A1 in to cells A2 and A3, and sheet1's values for these cell should appear. But what if you wanted to keep the value of A1 for some calculation. Yes you could amend the formula in cells A2 and A3 back to sheet1!A1, but this is awkward when you have many cells to change.

So change sheet 2 cell A1 to Sheet1!$A$1 and copy to cells A2 and A3 and you should get Sheet1 A1 value in all 3 cells.

HTH

Art

HTH

Art

[Boz]

Hi Art

Thaks for your details description! Do you know of any way that sets the links to 'absolute' without having to actually type the '$' symbols in the pasted cell adresses as I have LOADS to do!

Many thanks

Boz

[HiArt]

Boz,

not as such. You can get the same effect by pressing F4 once, before you finish typing in the formula.

Or...

You could type all the cell reference in and run some VBA code to turn all the cell addresses in to absolute values. To do this...

Press Alt + F11 to open the VBA Window.

Press Ctrl + R to open the Project window (might already be open)

Navigate to the VBAProject(workbook name), e.g. VBAProject(Book1)

Use menu item Insert>Module. A large white pane should be open to the right.

Cut'n'paste the following code.

Please Login or Register  to view this content.

To run, place the cursor in the first cell with a cell address, press Alt + F8 and select the "BozCode" macro.

Note that this code will stop at an empty cell in the column containing the addresses. Just move the cursor to the next non-empty cell and re-run the macro.

Also note I have assumed that you are putting the cell addresses in columns rather than rows. Just change
ActiveCell.Offset(1, 0).Activate
to
ActiveCell.Offset(0, 1).Activate
if you want to walk a row instead of a column.

HTH

Art

[Boz]

Wow...Thanks Art!!!

I've never done anything in VBA so is it ok to contact you if i get stuck?

Boz