Conditional Formatting... w/values?

Hi there. Is there anyway to do something like conditional formatting but instead of changing a cell's format it would change the cell's contents?

I build financial models for clients and have always gone to the IF statement in these situations [for example... =IF(F23="--",0,(G10^F10)/F4) ]. The bottleneck [and there's always a bottleneck] is that I need to edit the cell formula everytime I change one component of said formula.

What I'd like to do is to have the ability to simply copy and paste a value [any value or formula] into a cell and have an overarching tool [like conditional formatting] that will place a zero [0] into that same cell anytime some other condition is met.

Thanks a ton for any help!

Originally Posted by gunkie

Hi there. Is there anyway to do something like conditional formatting but instead of changing a cell's format it would change the cell's contents?

I build financial models for clients and have always gone to the IF statement in these situations [for example... =IF(F23="--",0,(G10^F10)/F4) ]. The bottleneck [and there's always a bottleneck] is that I need to edit the cell formula everytime I change one component of said formula.

What I'd like to do is to have the ability to simply copy and paste a value [any value or formula] into a cell and have an overarching tool [like conditional formatting] that will place a zero [0] into that same cell anytime some other condition is met.

Thanks a ton for any help!

This probably isn't what you're looking for... but it would work in some situations.

Using your example, if you want the answer in K23, put this in K23:
=IF(F23="--",0,J23)
or
=IF(OR(F23="--",ISERROR(J23)),0,J23)

Put your formula into J23:
=G10^F10/F4

This will product an error in certain situations (like F4=0, which is why you might want to use the ISERROR version above).

At this point, you hide column J. Column J contains the easily editable formula, while column K contains the conditional statement you want with your result. This depends a lot on whether or not you are able to hide columns on your spreadsheet. If you find such a process esthetically displeasing like some people seem to, you'll have to find another way. :-P

Someone else may have a better suggestion, but this has worked in the instances I've used it.

HTH,
Scott

Scott,

Thanks for the tip! I've done that before but I'm now building much larger models with much larger data matricies. Still, I may build up a hidden sheet that holds the values I need and put a simple IF statement in the sheet that I want to do my calculations on.

I just thought some neat tool was hiding in plain sight. It happens to me a lot

Thanks again!

Gunkie,

Have you considered using SCENARIOS

Ed