Thread: Refer to a windown whos name is derived from a cell value in a formula

Any help with this one would be greatly appreciated.

I want to tell the Index and Match formulas to refer to a window when the window's name is contained in an adjacent cell. The code below should make what I am trying to do clear:

Code:

Sub Macro2()

Dim ActWin As Window

ActWin = ActiveCell.Value & ".xlsx"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = _
 "=INDEX([ActWin]Sheet1!R3C[9]:R12C[9],MATCH(RC2,[ActWin]Sheet1!R3C10:R12C10,0))"

End Sub

However, I can't define "ActWin" in the way I have tried to above when it is defined as a Window. Changing the first line to "Dim ActWin As String" allows me to set ActWin equal to the value in the active cell however, I am then prompted to locate the file ActWin on my hard drive once the index/ Match formulas are put in.

Any help with this one would be greatly appreciated.

I want to tell the Index and Match formulas to refer to a window when the window's name is contained in an adjacent cell. The code below should make what I am trying to do clear:

Code:

Sub Macro2()

Dim ActWin As Window

ActWin = ActiveCell.Value & ".xlsx"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = _
 "=INDEX([ActWin]Sheet1!R3C[9]:R12C[9],MATCH(RC2,[ActWin]Sheet1!R3C10:R12C10,0))"

End Sub

However, I can't define "ActWin" in the way I have tried to above when it is defined as a Window. Changing the first line to "Dim ActWin As String" allows me to set ActWin equal to the value in the active cell however, I am then prompted to locate the file ActWin on my hard drive once the index/ Match formulas are put in.

Thanks a lot.
Works perfectly.

You have the variable name inside the quotation marks, so Excel thinks you want the exact letters ACTWIN inside the formula. You need to separate the text from the variable name as follows.

Code:

Sub Macro2()

ActiveCell.FormulaR1C1 = _
 "=INDEX(["&ActWin&"]Sheet1!R3C[9]:R12C[9],MATCH(RC2,["&ActWin&"]Sheet1!R3C10:R12C10,0))"

End Sub

Hope this helps

SAE