PDA

View Full Version : Linking to formula rather than paste special



ChristineJ
10-10-2009, 04:58 AM
As a simple example, =sum(a6:a26) is in cell E2 on Sheet1. I would like that same formula (not the result of that formula) to appear in cell E2 on Sheet2. I need to get it there by linking to E2 on Sheet1 rather than by copying and pasting special. Any way to do this?

Immatoity
10-10-2009, 05:50 AM
yes.. u dont need VBA... simply highlight/group all tabs (Sheets) in the workbook and enter that formula in cell e2...it will now be in all sheets..

remember to ungroup sheets using right click on any tab before doing anything else!

ChristineJ
10-10-2009, 06:17 AM
Thanks. One step further now. Can I get that same formula into cell E2 on Sheet1 in a linked workbook?

Immatoity
10-10-2009, 06:34 AM
yes..again you could use VBA but would be overkill..

open both workbooks

in cell E2 of the "other one" just type = and then with mouse point it to cell e2 on the other sheet.

( I am assuming you want the linked sheet to get its results from the "other" workbook)

EDIT :I am sure there are more "robust" VBA ways.. I am no expert!

EirikDaude
10-13-2009, 05:27 AM
I've got a somewhat similar problem, but the solution suggested here won't work very well in my case, since the amount of formulas I want to insert is just too great to do by hand (and it won't be the same in every worksheet).

I've come up with the following code:


For vekenr = 1 To 53
With Worksheets(CStr(vekenr))
For teljar = 1 To 18
Set til = Range(Cells(rekkenr, soylenr), Cells(rekkenr, soylenr))
(...)
Next
End With
Next vekeNr
With Sheets("Kompetanseoversikt")
Select Case omraade
Case "MF"
Select Case skiftNr
Case 1
til.Offset(-1, 2).Value = skiftNr
Set fra = Range("D6")
For teljar = 0 To 6
til.Offset(teljar, 0) = "=" + CStr(fra.Offset(teljar, 0).Address(External:=True))
Next
(...)
End Select
(...)
End Select
End With


But I can't seem to get it to work. Any suggestions on where I'm going wrong?