Text to formulas

[ivymike]

Here's a "cheater method" that might help some of you from time to time:

Suppose you have a spreadsheet with a whole bunch of data in columns, and you want to list specific outputs from a column in a row (like with paste special | transpose), and you want the output row to remain linked to the column data (unlike paste special | transpose).

You could use the index() function to select the desired data...

or you could cheat as follows - make your row with a couple example cells containing the desired formulas, except use ' to mark the formulas as text. Drag across the desired range to generate a row with text that displays the equations you'd like to have. Copy that row and paste into notepad, then copy from notepad and paste back into excel - presto! you now have "live" formulas instead of text.

ex:

'=A1 '=A3 '=A5 '=A7 '=A9 '=A11

copy into notepad and get

=A1 =A3 =A5 =A7 =A9 =A11

copy into excel and they're live...

 

[GregLocock]

There's a law against that! Other peculiar things I've done - write equations in MathCad, using its algebraic functions to come up with a single statement, then find and replace the variable names with cell names, then cut and paste into the spreadsheet.

Cheers

Greg Locock