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

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

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

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" <(E-Mail Removed)>
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

Here is another approach. Modify your formulas like this

=IF(P1="",SUMPRODUCT(--($J$2:$J$3100>5),--($K$2:$K$3100=1),--($L$2:$L$3100>51),--($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