Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    492
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Derive a Cell Reference in a formula, not the value

    Dear loungers,

    if I have "=OtherSheet!A9" as a formula in a cell can I get to the formula and use it (in an offset formula)

    liz

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,235
    Thanks
    202
    Thanked 796 Times in 729 Posts
    Liz,

    I couldn't figure a way to get it to work directly in the formula but here is a user defined function that will do the trick.
    Code:
    Option Explicit
    
    Public Function GetOffsetValue(zTarget As String, lRowOfs As Long, lColOfs As Long) As Variant
    
       Dim zFormula As String
       Dim vValue   As Variant
       
       zFormula = Range(zTarget).Formula
       GetOffsetValue = Range(zFormula).Offset(lRowOfs, lColOfs)
    
    End Function
    Calling sequence: =GetOffsetValue(cell_containing_offsheet_reference ,row_offset,column_offset)
    Where: cell_contining_offsheet_reference is a string containing an Absolute Reference, e.g. $A$1
    row_offset is the number of rows to offset on the target sheet from the target reference in cell_containing_offsheet_reference.
    col_offset is the number of columns to offset on the target sheet from the target reference in cell_containing_offsheet_reference.
    IndireceOffsets.PNG
    Ex: if Cell A1 contains the off sheet reference =Othersheet!A1 and you want the value from the 3rd row down in the same column then:
    =GetOffsetValue("$A$1",3,0)

    Hope this helps. Maybe someone else can find an easier method.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    lizat (2012-11-05)

  4. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    492
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thank you Retired Geek, I have several solutions from you in the past.... "retired" is certainly not what you are!

Posting Permissions

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