Creating Live Links from Indirect() Formulae?

    Hi,


    I find Indirect() to be really useful in quickly creating output tables of large workbooks, but it is really annoying that you can't then navigate back to the cell it is displaying (say, using Ctrl { ).


    I'm just wondering if anybody has ever written something which would evaluate indirect functions on a sheet and rebuild the formula so that it creates a proper "live" link?


    I suppose a simpler approach would be to build a hyperlink based on the formula, whick links to the cell.


    Long shot, but would be keen to see if anybody has already tackled this..


    Cheers,
    Mike

    Re: Creating Live Links from Indirect() Formulae?


    Hmm. I'm curious as to the situation where you find the indirect formula easier to use to create a table and then want to change it as you state.


    I've only given this a limited test, but see if it works for you. It looks for Indirect functions in the selected range and replaces them. It assumes the Indirect argument is valid.


    [vba]Sub ChangeIndirect()
    Dim c As Range, x As String, s1 As String, s2 As String, s3 As String, i As Integer, j As Integer
    For Each c In Selection
    If c.HasFormula Then
    i = InStr(c.Formula, "INDIRECT(")
    If i > 0 Then
    s1 = Left(c.Formula, i - 1)
    s2 = Mid(c.Formula, i + 9)
    j = 0
    i = 0
    While j >= 0
    i = i + 1
    x = Mid(s2, i, 1)
    If x = ")" Then
    j = j - 1
    ElseIf x = "(" Then
    j = j + 1
    End If
    Wend
    If i < Len(s2) Then s3 = Mid(s2, i + 1) Else s3 = ""
    s2 = Evaluate(Left(s2, i - 1))
    c.Formula = s1 & s2 & s3
    End If
    End If
    Next c
    End Sub[/vba]

    Re: Creating Live Links from Indirect() Formulae?


    Derk,


    That is just brilliant! I create outputs from 100-odd sheet workbooks, and navigating to the sheet to build/link the output page is painful. Thankfully, the sheets are standard so indirects work.
    The trouble is that if a number loooks wrong, I can't easily navigate to that sheet and cell to check it. (Ctrl - { is a godsend).


    This works superbly!


    Thanks very much.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login