Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Formulae created from text strings will not compute.

In anExcel 2000 workbook I have created a series of formulae in a column
using a text string. To create the text string I used a different formula
initially to pull together a mixture of text and the contents of cells in
another column. (There is a very good reason for this.) I then copy and
special paste the "text strings" in the new column as values. At this point
all the cells appear to be formulae (using cells from another sheet in the
same workbook), but they do not compute - not until I click on each
individual cell with my mouse and press return. THEN the newly created
formula in one cell computes, and returns the answer. I can only perform this
task for one cell at a time: I can't get the whole column of newly created
formulae to compute all at once.

If you understood this, you're a real brain box, but is there a solution to
getting all the column to compute at once?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
p p is offline
external usenet poster
 
Posts: 12
Default Formulae created from text strings will not compute.

as i understood your practical question..
i test run one sample
on column A, format cell into text.
i type =1+1 cell appears as =1+1
then i copy Column A paste special values to column B
I select whole column B
Click EditFind
Find =
Replace =
replace all
then see result as 2 on all cells of column B.

logistics is the name not the brainbox.



"Bart Wolf" wrote:

In anExcel 2000 workbook I have created a series of formulae in a column
using a text string. To create the text string I used a different formula
initially to pull together a mixture of text and the contents of cells in
another column. (There is a very good reason for this.) I then copy and
special paste the "text strings" in the new column as values. At this point
all the cells appear to be formulae (using cells from another sheet in the
same workbook), but they do not compute - not until I click on each
individual cell with my mouse and press return. THEN the newly created
formula in one cell computes, and returns the answer. I can only perform this
task for one cell at a time: I can't get the whole column of newly created
formulae to compute all at once.

If you understood this, you're a real brain box, but is there a solution to
getting all the column to compute at once?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Formulae created from text strings will not compute.

You could create a User Defined Function along the lines of:

Function Eval(Rng As Range) As Variant
Application.Volatile True
Eval = Evaluate(Rng.Text)
End Function

Then, call this as =Eval(A2) in cell B2, where the text strings
representing the formula are in column A. Copy this formula down column
B and it will return the result of the "formulae" in column A.

Hope this helps.

Pete


p wrote:

as i understood your practical question..
i test run one sample
on column A, format cell into text.
i type =1+1 cell appears as =1+1
then i copy Column A paste special values to column B
I select whole column B
Click EditFind
Find =
Replace =
replace all
then see result as 2 on all cells of column B.

logistics is the name not the brainbox.



"Bart Wolf" wrote:

In anExcel 2000 workbook I have created a series of formulae in a column
using a text string. To create the text string I used a different formula
initially to pull together a mixture of text and the contents of cells in
another column. (There is a very good reason for this.) I then copy and
special paste the "text strings" in the new column as values. At this point
all the cells appear to be formulae (using cells from another sheet in the
same workbook), but they do not compute - not until I click on each
individual cell with my mouse and press return. THEN the newly created
formula in one cell computes, and returns the answer. I can only perform this
task for one cell at a time: I can't get the whole column of newly created
formulae to compute all at once.

If you understood this, you're a real brain box, but is there a solution to
getting all the column to compute at once?


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 340
Default Formulae created from text strings will not compute.

Another approach: select column and then Data Text to Columns
Finish.

p wrote:

as i understood your practical question..
i test run one sample
on column A, format cell into text.
i type =1+1 cell appears as =1+1
then i copy Column A paste special values to column B
I select whole column B
Click EditFind
Find =
Replace =
replace all
then see result as 2 on all cells of column B.

logistics is the name not the brainbox.



"Bart Wolf" wrote:

In anExcel 2000 workbook I have created a series of formulae in a column
using a text string. To create the text string I used a different formula
initially to pull together a mixture of text and the contents of cells in
another column. (There is a very good reason for this.) I then copy and
special paste the "text strings" in the new column as values. At this point
all the cells appear to be formulae (using cells from another sheet in the
same workbook), but they do not compute - not until I click on each
individual cell with my mouse and press return. THEN the newly created
formula in one cell computes, and returns the answer. I can only perform this
task for one cell at a time: I can't get the whole column of newly created
formulae to compute all at once.

If you understood this, you're a real brain box, but is there a solution to
getting all the column to compute at once?


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Formulae created from text strings will not compute.

You're not a brain box: you're a genius!

I did what you said, and, hey presto! It's so simple!
It works on my formulae too.

Thanks
Bart

"p" wrote:

as i understood your practical question..
i test run one sample
on column A, format cell into text.
i type =1+1 cell appears as =1+1
then i copy Column A paste special values to column B
I select whole column B
Click EditFind
Find =
Replace =
replace all
then see result as 2 on all cells of column B.

logistics is the name not the brainbox.



"Bart Wolf" wrote:

In anExcel 2000 workbook I have created a series of formulae in a column
using a text string. To create the text string I used a different formula
initially to pull together a mixture of text and the contents of cells in
another column. (There is a very good reason for this.) I then copy and
special paste the "text strings" in the new column as values. At this point
all the cells appear to be formulae (using cells from another sheet in the
same workbook), but they do not compute - not until I click on each
individual cell with my mouse and press return. THEN the newly created
formula in one cell computes, and returns the answer. I can only perform this
task for one cell at a time: I can't get the whole column of newly created
formulae to compute all at once.

If you understood this, you're a real brain box, but is there a solution to
getting all the column to compute at once?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Formulae created from text strings will not compute.

Hi, Lori.
Yes, your approach also works!
It's as if the formulae need that extra "push" to complete the computation.
Don't understand why, though.

Thanks again,
Bart

"Lori" wrote:

Another approach: select column and then Data Text to Columns
Finish.

p wrote:

as i understood your practical question..
i test run one sample
on column A, format cell into text.
i type =1+1 cell appears as =1+1
then i copy Column A paste special values to column B
I select whole column B
Click EditFind
Find =
Replace =
replace all
then see result as 2 on all cells of column B.

logistics is the name not the brainbox.



"Bart Wolf" wrote:

In anExcel 2000 workbook I have created a series of formulae in a column
using a text string. To create the text string I used a different formula
initially to pull together a mixture of text and the contents of cells in
another column. (There is a very good reason for this.) I then copy and
special paste the "text strings" in the new column as values. At this point
all the cells appear to be formulae (using cells from another sheet in the
same workbook), but they do not compute - not until I click on each
individual cell with my mouse and press return. THEN the newly created
formula in one cell computes, and returns the answer. I can only perform this
task for one cell at a time: I can't get the whole column of newly created
formulae to compute all at once.

If you understood this, you're a real brain box, but is there a solution to
getting all the column to compute at once?



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Formulae created from text strings will not compute.

I ought to get into this; but, since the two other suggestions worked,
that'll do me fine!
I gues your solution would be useful in a macro file.

Cheers,
Bart

"Pete_UK" wrote:

You could create a User Defined Function along the lines of:

Function Eval(Rng As Range) As Variant
Application.Volatile True
Eval = Evaluate(Rng.Text)
End Function

Then, call this as =Eval(A2) in cell B2, where the text strings
representing the formula are in column A. Copy this formula down column
B and it will return the result of the "formulae" in column A.

Hope this helps.

Pete


p wrote:

as i understood your practical question..
i test run one sample
on column A, format cell into text.
i type =1+1 cell appears as =1+1
then i copy Column A paste special values to column B
I select whole column B
Click EditFind
Find =
Replace =
replace all
then see result as 2 on all cells of column B.

logistics is the name not the brainbox.



"Bart Wolf" wrote:

In anExcel 2000 workbook I have created a series of formulae in a column
using a text string. To create the text string I used a different formula
initially to pull together a mixture of text and the contents of cells in
another column. (There is a very good reason for this.) I then copy and
special paste the "text strings" in the new column as values. At this point
all the cells appear to be formulae (using cells from another sheet in the
same workbook), but they do not compute - not until I click on each
individual cell with my mouse and press return. THEN the newly created
formula in one cell computes, and returns the answer. I can only perform this
task for one cell at a time: I can't get the whole column of newly created
formulae to compute all at once.

If you understood this, you're a real brain box, but is there a solution to
getting all the column to compute at once?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
truncating text strings of different lengths Funkey Excel Discussion (Misc queries) 7 March 29th 06 08:22 PM
Splitting text strings along commas. Andrew Excel Discussion (Misc queries) 3 January 10th 06 05:01 PM
text box to contain formulae results that recalculate Jack Sons Excel Discussion (Misc queries) 2 October 21st 05 02:43 PM
How do I add help text to an excel function I have created Peter 99 Excel Worksheet Functions 2 January 14th 05 07:04 PM
Filter long Text strings Cimorene Excel Worksheet Functions 0 December 1st 04 05:01 AM


All times are GMT +1. The time now is 10:47 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"