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]

I think I'm on the right track. I created a macro to copy the functions and then replace all of the X's with A7. I then concatenate an equal sign

="="&A5 to get the proper text that should be entered as a formula, but the cell recognizes this as text. How do I convert the text into a formula?

E.g. in cell B7 I have "=a7^2" (text), but I want =a7^2 (a formula).

Here is a link to what I've started: http://dl.dropbox.com/u/7650663/1D%20Motion%20with%20graphs.xls

Thanks!

 

[jae113]

I did this: =TEXT(A6,"????") & "^ 2"

It brings over the value from A6 - A65 and combines it with the text "^ 2".

Does that help?

 

[jeffcia]

I did this: =TEXT(A6,"????") & "^ 2"

It brings over the value from A6 - A65 and combines it with the text "^ 2".

Does that help?

Not really. I want to be able to enter any function and automate it from there, and I need it to be a formula not text

 

[jae113]

What about if you format the cell as a formula?

I looked at your sheet, but I'm not sure I understand what you're wanting to do. Maybe try to repost but use a different title. Like Macro help or something to that effect

 

[jeffcia]

Formatting it won't actually change the contents

 

[Richard Schollar]

Here's one possible way:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td width="25px" style="color: #161120;text-align: center;">4</td><td width="63px" style=";">X^3</td><td width="63px" style="text-align: right;;"></td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">5</td><td width="63px" style="text-align: right;;"></td><td width="63px" style="text-align: right;;"></td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">6</td><td width="63px" style="text-align: right;;">1</td><td width="63px" style="text-align: right;;">1</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">7</td><td width="63px" style="text-align: right;;">2</td><td width="63px" style="text-align: right;;">8</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">8</td><td width="63px" style="text-align: right;;">3</td><td width="63px" style="text-align: right;;">27</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">9</td><td width="63px" style="text-align: right;;">4</td><td width="63px" style="text-align: right;;">64</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">10</td><td width="63px" style="text-align: right;;">5</td><td width="63px" style="text-align: right;;">125</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">11</td><td width="63px" style="text-align: right;;">6</td><td width="63px" style="text-align: right;;">216</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">12</td><td width="63px" style="text-align: right;;">7</td><td width="63px" style="text-align: right;;">343</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">13</td><td width="63px" style="text-align: right;;">8</td><td width="63px" style="text-align: right;;">512</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">14</td><td width="63px" style="text-align: right;;">9</td><td width="63px" style="text-align: right;;">729</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">15</td><td width="63px" style="text-align: right;;">10</td><td width="63px" style="text-align: right;;">1000</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B6</th><td style="text-align:left">=eval</td></tr></tbody></table></td></tr></table><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">eval</th><td style="text-align:left">=EVALUATE(<font color="Blue">SUBSTITUTE(<font color="Red">Sheet1!$A$4,"X",INDIRECT(<font color="Green">"RC[-1]",0</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Richard Schollar]

Note if you use a compound formula like X^2+4X-10 then you need to write it like this:
X^2+4*X-10 (note the * between the 4 and the X).

 

[jeffcia]

Thanks Richard.

 

[neilkeron]

I may be missing something here but don't you just need to create named ranges. If you type "X" in to cell A1, select cells A1 to say A10, go to Insert/Name/Create, check the 'Top row' box and click OK. The range A2 to A10 is now named "X" but is dynamic. If you then put the formula "=X^2" in to cell B2 and copy down to B10, excel calculates the formula but is smart enough to recognise that in cell B3 the value of X that it should use is the value in cell A3.

Hope this helps
Regards
Neil Keron