Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate Text to create Formula
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate Text to create Formula
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 "Brendon" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONCATENATE text to create a formula to be evaluated | Excel Worksheet Functions | |||
How to concatenate text into a formula? | Excel Discussion (Misc queries) | |||
Formula needed to concatenate text with result from calculation | Excel Discussion (Misc queries) | |||
how can i use concatenate to create a linking formula? | Excel Worksheet Functions | |||
CONCATENATE text formula | Excel Worksheet Functions |