Variable string for cell reference

[TMacorke]

I'm trying to write a formula in a cell that will allow the file name to vary.
The first three characters of the file name will change depending on the user input of another field on the spreadsheet. I have written the formula below, which DISPLAYS the exact formula that I want excel to EXECUTE....it just doesn't execute the formula in the cell.

="'["&RIGHT(B4,3)&"-DemandPlan-MasterFile V5b.xls]WorldWide'!Q$10" is the formula
'[RAA-DemandPlan-MasterFile V5b.xls]WorldWide'!Q$10 is the results....

the results of this should be the value in cell Q10 from file named RAA-DemandPlan-MasterFile V5b.xls.

How do I get excel to execute the formula in order to display the value from Q10?:help

[mvidas]

Hello,
You should be able to use the indirect function with your current function, like:

=INDIRECT("'["&RIGHT(B4,3)&"-DemandPlan-MasterFile V5b.xls]WorldWide'!Q$10")

But note that if your filename or sheet name doesnt have a space in it, you'll get a #REF error. But since you obviously will have a space, it should work as above.
Matt

[Richie(UK)]

Hi TM,

Just on my way out the door so a bit lacking in detail ;)

Try the Indirect function.

[TMacorke]

OH YES. That's it.

THANK YOU!

[ulfal028]

And how would I do it in VBA?

Example: Change RowSource reference to be INDIRECT instead of direct

Private Sub UserForm_Initialize()
With ListBox1
.Clear
.RowSource = "Sheet1!A2:A10"
.ControlSource = "Sheet1!C2"
.ColumnHeads = True
End With
End Sub