Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
truncating text strings of different lengths | Excel Discussion (Misc queries) | |||
Splitting text strings along commas. | Excel Discussion (Misc queries) | |||
text box to contain formulae results that recalculate | Excel Discussion (Misc queries) | |||
How do I add help text to an excel function I have created | Excel Worksheet Functions | |||
Filter long Text strings | Excel Worksheet Functions |