Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Equations in EXCEL (EXCEL 97 SR2)

    I am new to EXCEL and have a question concerning the copying of an equation from worksheet to worksheet. I have 4 worksheets. In cell A1 of the first worksheet I have the value 1. In the second worksheet I have the equation which references the cell A1 in sheet 1 and add the value of 1. Resulting in the value of 2. I then copy this equation to the 3rd and 4th worksheet and find the equation continues to reference the first sheet, as thought the first sheet has an absolute reference. I want it to reference the sheet immediately in front of it. How can I do that without hard coding each equation in each worksheet? Any thought will be helpful. Thanks !!!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Equations in EXCEL (EXCEL 97 SR2)

    You could use the User Defined Function below:

    <pre>Public Function GetPrevSheet(oCell As Range) As Variant
    Dim iPrev As Integer
    If ActiveSheet.Index = 1 Then
    iPrev = Worksheets.Count
    Else
    iPrev = ActiveSheet.Index - 1
    End If
    GetPrevSheet = Worksheets(iPrev).Range(oCell.Address).Value
    End Function
    </pre>


    Then put the formula blow in the second sheet and copy it from sheet to sheet:

    <pre>=getprevsheet(A1)+1
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •