Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert formula to text
I have a very long formula using
sumprodct(--isna(match(.....)),--match(....)). IN this formula, i'm comparing four columns and these columns are defined in four cells because they are not constant. The problem with this formula is that it is extremely slowing down the excel file. Any change made takes a long time to recalculate, but I can't turn calculation manual because I need other cells to change. Now that the formula is set in a cell, can I use a function/macro to convert the formula to text when this formula is not used and convert it back to formula when used? Thanks, Carmen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert formula to text
Maybe this:
Select the cells to be impacted This will convert formulas to text: [Ctrl]+H.........a shortcut for <edit<replace Find what: =SUMPRODUCT Replace with: ||=SUMPRODUCT Click either [Replace] or [Replace All]....depending on the selection This will make them formulas again: [Ctrl]+H.........a shortcut for <edit<replace Find what: ||=SUMPRODUCT Replace with: =SUMPRODUCT Click either [Replace] or [Replace All]....depending on the selection Note: Depending on your formula structure you may be able to search for: =SUM or maybe even: = Is that something you can work with? *********** Regards, Ron XL2003, WinXP "Carmen" wrote: I have a very long formula using sumprodct(--isna(match(.....)),--match(....)). IN this formula, i'm comparing four columns and these columns are defined in four cells because they are not constant. The problem with this formula is that it is extremely slowing down the excel file. Any change made takes a long time to recalculate, but I can't turn calculation manual because I need other cells to change. Now that the formula is set in a cell, can I use a function/macro to convert the formula to text when this formula is not used and convert it back to formula when used? Thanks, Carmen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert formula to text
One more thought.....if it's only 1 cell with that formula:
Select the cell [F2]...................to edit the cell [Ctrl]+[Home].....to move to the beginning of the formula Type '.....that's an apostrophe.....Press [Enter] (now the cell is text) To restore the formula Follow the same instructions....but, remove the apostrophe. Does that help? *********** Regards, Ron XL2003, WinXP "Ron Coderre" wrote: Maybe this: Select the cells to be impacted This will convert formulas to text: [Ctrl]+H.........a shortcut for <edit<replace Find what: =SUMPRODUCT Replace with: ||=SUMPRODUCT Click either [Replace] or [Replace All]....depending on the selection This will make them formulas again: [Ctrl]+H.........a shortcut for <edit<replace Find what: ||=SUMPRODUCT Replace with: =SUMPRODUCT Click either [Replace] or [Replace All]....depending on the selection Note: Depending on your formula structure you may be able to search for: =SUM or maybe even: = Is that something you can work with? *********** Regards, Ron XL2003, WinXP "Carmen" wrote: I have a very long formula using sumprodct(--isna(match(.....)),--match(....)). IN this formula, i'm comparing four columns and these columns are defined in four cells because they are not constant. The problem with this formula is that it is extremely slowing down the excel file. Any change made takes a long time to recalculate, but I can't turn calculation manual because I need other cells to change. Now that the formula is set in a cell, can I use a function/macro to convert the formula to text when this formula is not used and convert it back to formula when used? Thanks, Carmen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert formula to text
You could also consider changing the formula to a Database function (DGet ?). -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Carmen" wrote in message I have a very long formula using sumprodct(--isna(match(.....)),--match(....)). IN this formula, i'm comparing four columns and these columns are defined in four cells because they are not constant. The problem with this formula is that it is extremely slowing down the excel file. Any change made takes a long time to recalculate, but I can't turn calculation manual because I need other cells to change. Now that the formula is set in a cell, can I use a function/macro to convert the formula to text when this formula is not used and convert it back to formula when used? Thanks, Carmen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert formula to text
Here is another approach. Modify your formulas like this
=IF(P1="",SUMPRODUCT(--($J$2:$J$31005),--($K$2:$K$3100=1),--($L$2:$L$310051),--($M$2:$M$3100=1),$N$2:$N$3100),"") then if P1 is anything but blank, the Sumproduct part is not recalculated. Clear P1 and it recalculates normally. Use any cell you want to control the calculation. -- Regards, Tom Ogilvy "Carmen" wrote: I have a very long formula using sumprodct(--isna(match(.....)),--match(....)). IN this formula, i'm comparing four columns and these columns are defined in four cells because they are not constant. The problem with this formula is that it is extremely slowing down the excel file. Any change made takes a long time to recalculate, but I can't turn calculation manual because I need other cells to change. Now that the formula is set in a cell, can I use a function/macro to convert the formula to text when this formula is not used and convert it back to formula when used? Thanks, Carmen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Text to a Formula | Excel Discussion (Misc queries) | |||
Convert Text to Formula | Excel Worksheet Functions | |||
How to convert formula to text | Excel Worksheet Functions | |||
convert text into Formula | Excel Discussion (Misc queries) | |||
how to convert a formula into text in order to display the formula | Excel Discussion (Misc queries) |