Can you create a name for a column?

[jeffcia]

I am trying to create a sheet so that students can put in a function such as X^2 and then see the graph. Is there a way to define "X" that is dynamic?

For example, I have text "x^2" in cell B1, and then I have values for X in A6:A56. In cells B6:B56, I would like to change the "X" to A6, A7, ... A56, so I think I'm going to need a text function to replace X with A.

Thoughts? Thanks in Advance!

 

[jeffcia]

Really? That's great! How far back does that go? Some of my users may have a much older version. Thanks!

 

[neilkeron]

I'm still using Excel 2003 at home and it work for that.
Regards
Neil

 

[jeffcia]

I would like to enter text in the top cell and then have the cells below turn that text into a function. I tried =eval(B1), and this works for the first cell only, but all the cells below that give the same value.

When I put the formula directly into the cells in the range, the name is dynamic.

Maybe I can go the other way around. Can somebody help me display a formula as text in a different cell? Can I simply concatenate somehow?

 

[neilkeron]

I found this bit of code on the internet a while ago. It converts a formula in to a text string. You need to set it up as an add-in and then enter =getformula(b2) in to cell b1 (assuming your X title is in cell A1)

Function GetFormula(Cell As Range) As String
GetFormula = Cell.Formula
End Function

Function GetFormat(Cell As Range) As String
GetFormat = Cell.NumberFormat
End Function
Function ExtractNumber(rCell As Range, _
Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double

Dim iCount As Integer, i As Integer, iLoop As Integer

Dim sText As String, strNeg As String, strDec As String

Dim lNum As String

Dim vVal, vVal2


''''''''''''''''''''''''''''''''''''''''''

'Written by OzGrid Business Applications

'www.ozgrid.com



'Extracts a number from a cell containing text and numbers.
''''''''''''''''''''''''''''''''''''''''''
sText = rCell

If Take_decimal = True And Take_negative = True Then

strNeg = "-" 'Negative Sign MUST be before 1st number.

strDec = "."

ElseIf Take_decimal = True And Take_negative = False Then

strNeg = vbNullString

strDec = "."

ElseIf Take_decimal = False And Take_negative = True Then

strNeg = "-"

strDec = vbNullString

End If

iLoop = Len(sText)

For iCount = iLoop To 1 Step -1

vVal = Mid(sText, iCount, 1)


If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then

i = i + 1

lNum = Mid(sText, iCount, 1) & lNum

If IsNumeric(lNum) Then

If CDbl(lNum) < 0 Then Exit For

Else

lNum = Replace(lNum, Left(lNum, 1), "", , 1)

End If

End If

If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))

Next iCount


ExtractNumber = CDbl(lNum)


End Function


Good luck

Neil

 

[jeffcia]

Cool, thanks!