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...
Option Explicit Sub formulaset() Dim Cell As Range Dim target As String For Each Cell In Range("b4:al132") Application.ScreenUpdating = False target = Cell.Address Cell.FormulaR1C1 = "=INDIRECT(CONCATENATE(BackEnd!E15,""!"",target))" Application.ScreenUpdating = True Next Cell End Sub
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.