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

    Relative sheet reference (97/SR-1)

    This may have been answered in the past, but since the search function is disabled....

    I have a file with several sheets. Named Jan-Dec (or 1-22, or whatever). I enter a formula in sheet 13 such as ='Jan'!$A$1. I would like to copy that formula to the next 11 cells in a row, with the sheet name being relative. Is this possible?

    I know there is a way to make sheet references relative by this VBA function:
    Function SHEETOFFSET(offset, Ref)
    ' Returns cell contents at Ref, in sheet offset
    With Application.Caller.Parent
    SHEETOFFSET = .Parent.Sheets(.Index + offset) _
    End With
    End Function
    But this only works if you copy across sheets, not cells. Can this be modified to do what I want? I'm not very good at VBA or macros, but have worked with them a little.

    Thanks for your help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Relative sheet reference (97/SR-1)

    You could use the INDIRECT function to get the sheet name. Just include a column and enter the sheet names. If col A has SHeet namestheis formula copied down the rows will put the value of A1 from each of the sheetnames listed in Col A:


    You could make it even more "generic" by also using ADDRESS and adding info on row and col number:


    will give the contents of A1 from the sheetname listed in cell A1


    will give the contents of C2 from the sheetname listed in cell A3


Posting Permissions

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