Concatenate Text to create Formula

[Brendon]

I am trying to concatenate text from several cells to
create a formula.
I can do this to display as text then copy and paste
special as values into a new cell. This displays still as
text, but if I then edit the cell by pressing "F2" then
press enter without changing anything this converts the
text into a formula.
The problem with doing it this way is I need this to
become a formula automatically without having to edit the
cell to get it to become a formula. As the copy paste
special function is part of a macro, the macro wont allow
me to edit each cell without displaying the exact
contents of the cell, which change on a daily basis.

The cell contents are as follows
=CONCATENATE("='\\server\FO_Data\EXPORT\[Trial
Balance",$A$1,$B$2,$B$1,$C$2,$C$1,".XLS]Sheet1'!B8")

Cells $A$1,$B$2,$B$1,$C$2,$C$1 are all variables that
change, so the only way I can work out how to do this is
to use the concatenate function.

I am using Excel XP.
Thanks in anticipation of your wisdom and help.

 

[Bernie Deitrick]

Brendon,

You could use a macro: this example will create a formula in cell B8 of the activesheet:

Sub MakeFormula()

Range("B8").Formula = _
"='\\server\FO_Data\EXPORT\[Trial Balance" & _
Range("$A$1").Value & _
Range("$B$2").Value & _
Range("$B$1").Value & _
Range("$C$2").Value & _
Range("$C$1").Value & _
".XLS]Sheet1'!B8"

End Sub

HTH,
Bernie
Excel MVP