Add Indirect Formula Via Macro

  • I need to create a formula for a series of ranges that have a variable sheet name (which is located on sheet Backend!E15) and when it creates the formula will reference the exact same cell on the variable sheet.

    this is what i have so far...

    but this is the answer I am getting in the first cell of the range...


    as you can see I am having trouble getting the target address to lock in. To make things worse, its needs to be in " " so the concatenate creates the corect address link.

    Any help is much appreciated.

  • Re: Indirect Vba Formula

    here is the other problem. Where the "Target" is entered in the has to be in " ". So that it is observed as text for the Concatenate to work. The indirect function activates after reading the completed concatenate.

    I have been looking at this for so long its killing me.

  • Re: Add Indirect Formula Via Macro

    thanks to all for the help. I finally got it...

    Here is the solution.

    target = Cell.Address
        Cell.Formula = "=INDIRECT(CONCATENATE(" & Range("BackEnd!E15") _
            .Address(, , , True) & ",""!"",""" & target & """))"

    Again, I needed the cell address to be entered as " ".

    Dave, By recording it sets an actual reference to the cell it ends up being a circular reference since the concatenate is trying to use the address as opposed to joining the "name" of an address. Thanks anyway...the recorder is actually what I used to get me started.

Participate now!

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