concatenated text to formula

[Bill Elerding]

I have concatenated a number of cells to make a formula that I will update
data on a weekly basis. Unfortunately, the formula is in text currently, as
noted below:

'=CONCATENATE(Q5,R5,S5,V5,Q5,P5,N5,W5,N5,O5,T5)

When I try to replace the ' it does not find it. I presume this is
because it is currently text. Formating the column as either General or
Number does not seem to work. Conversely, if I enter the cells and back
space over the ' , it works fine.

And this is what it looks like when I copy it over into it's destination
cell before deleting the ' :
=if(Data!c44="D",Data!k44,"")

Any thoughts or macro's I can try to have a functioning formula?

THANKS!

 

[Biff]

Hi!

You can't "build" a formula like that.

You could do something like this:

A1 = Data!
B1 = text entry C44
B2 = text entry K44
C1 = D
D1= formula =""

=IF(INDIRECT(A1&B1)=C1,INDIRECT(A1&B2),D1)

Which evaluates to:

=IF(Data!C44="D",Data!K44,"")

Biff

 

[Gord Dibben]

Bill

Get rid of the '= part of the text so's you have just the

CONCATENATE(Q5,R5,S5,V5,Q5,P5,N5,W5,N5,O5,T5) in the cell.

Then copy/paste this UDF into a module.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

Usage is =EvalCell(cellref) where cellref is the cell with the above text.


Gord Dibben Excel MVP

 

[David McRitchie]

You could use a macro to reenter the cells that are text and begin
with an "=" sign. As long as you are NOT trying to create these on
the fly but to make a permanent set of formulas you technique would
then work. Macro will not pick up the leading apostrophe that is
used to create a text field. For this to work you must not have
formatted the cells to be change as text as you will simply get
text again -- no actual change.

Option Explicit

Sub Convert_Equalsigns()
'David McRitchie 2005-05-01, misc
' modified from TRIMALL macro in join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Formula = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub



--

 

[Bill Elerding]

Thanks, Biff. This worked great, and I really appreciate the help. So much
to learn, and so little time...

Have a great evening!

 

[Bill Elerding]

Thanks, Gord. I tried Biff's recommendation first, though will be trying
yours this evening. I really appreciate the help. The contatenation really
caused me some problems.

 

[Bill Elerding]

Thanks, Dave. It has taken me a few days to get back. With so many sign-ons
and passwords, I forgot the one for this site.

I'm going to copy this into another spreadsheet I am using, tonight. Biff's
option worked for me with the current problem I've had, but this
contatenation thing has caused me some problems. Thanks for the great
solution.

I really appreciate the help!
--
William Elerding

You could use a macro to reenter the cells that are text and begin
with an "=" sign. As long as you are NOT trying to create these on
the fly but to make a permanent set of formulas you technique would
then work. Macro will not pick up the leading apostrophe that is
used to create a text field. For this to work you must not have
formatted the cells to be change as text as you will simply get
text again -- no actual change.

Option Explicit

Sub Convert_Equalsigns()
'David McRitchie 2005-05-01, misc
' modified from TRIMALL macro in join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Formula = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub



--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I have concatenated a number of cells to make a formula that I will update
data on a weekly basis. Unfortunately, the formula is in text currently, as
noted below:

'=CONCATENATE(Q5,R5,S5,V5,Q5,P5,N5,W5,N5,O5,T5)

When I try to replace the ' it does not find it. I presume this is
because it is currently text. Formating the column as either General or
Number does not seem to work. Conversely, if I enter the cells and back
space over the ' , it works fine.

And this is what it looks like when I copy it over into it's destination
cell before deleting the ' :
=if(Data!c44="D",Data!k44,"")

Any thoughts or macro's I can try to have a functioning formula?

THANKS!