Variable Frame Reference
 

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?

Variable Frame Reference
 

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


On Wed, 17 Feb 2010 12:24:55 -0800 (PST), Branden Durst
wrote:

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?