Variable Frame Reference

[Branden Durst]

I am in the process of creating a very large excel spreadsheet and am
hoping to find a workaround. My intermediate excel skills have let me
down!

Here is what I'd like to do. In the first worksheet "Sheet 1" I have
a list of users and corresponding data. I have a subsequent worksheet
for each user. What I'd like to be able to do is have the number of
the row change based upon the new worksheet. In other words, I'd like
to have Cell A1 in Sheet 2 reference Cell A2 in Sheet 1, Cell A1 in
Sheet 3 reference Cell A3 in Sheet 1, Cell A1 in Sheet 4 reference
Cell A4 in Sheet 1, and so on. Can this be done?

 

[Gord Dibben]

Sub Formula_Increment()
Dim mytext As String
Dim iCtr As Long
mytext = "=Sheet1!A"
For iCtr = 2 To Worksheets.Count
With Worksheets(iCtr).Range("A1")
.Value = mytext & iCtr - 1
End With
Next iCtr
End Sub


Gord Dibben MS Excel MVP