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

[samnic]

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:

Please Login or Register  to view this content.

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.

[samnic]

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:

Please Login or Register  to view this content.

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.

[6StringJazzer]

You may have a misunderstanding of what Window is. When you talk about a Window's name, I think you really mean a Workbook's name. Window does not have a Name attribute. Regardless, this code just works with strings and doesn't really have to know anything about workbooks. I think you want something like this:

Please Login or Register  to view this content.

Excel will then try to evaluate the formula and if the file doesn't already exist it requires you to locate it. That is not an issue with VBA, it's just how external workbook references work when using INDEX. The same thing would happen if you just typed in the formula directly into a cell.

[samnic]

Thanks a lot.
Works perfectly.

[seckert]

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.

Please Login or Register  to view this content.

Hope this helps

SAE

[teylyn]

Samnic, I've merged your two identical questions into one thread. In the future please take care not to post the same question twice.

thanks