Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    490
    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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,965
    Thanks
    193
    Thanked 730 Times in 666 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


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

    lizat (2012-11-05)

  5. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    490
    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
  •