#### Overwriting a cell with a formula without deleting the formula

```Hello.  I am creating an Expense Report worksheet and have created a simple
formula that will calculate mileage based on total miles.  Below is my
worksheet data.

A                              B                         C
D
1  Expense Type            Acct. Code        Total Miles	       Amount
2  Airfare	                   11111
\$250.00
3  Mileage	                   22222                     20
\$10.00

I am trying to figure out a way to create a conditional formula so that IF
Expense Type is "Mileage", data in "Total Miles" must not be blank, and the
"Amount" will automatically calculate based on the formula "C3*.50".

Additionally, I want to be able to enter an Amount in Column D without
overwriting the underlying formula.  For example, say in A2 I enter Expense
Type "Airfare" and then enter the total amount of \$250.00 Column D.  Once I
enter data in Column D, I overwrite the formula.  If I need to go back and
change the Expense Type to "Mileage", Column D won't automatically calculate
anymore.  By typing data into this cell, I have essentially deleted the
formula.  Is there a way to maintain the underlying conditional formula while
at the same allowing me to manually enter data in that cell without losing
the formula?

``` 0  Utf
1/16/2010 12:39:01 AM excel.worksheet.functions  4936 articles. 2 followers. 1 Replies 2717 Views Similar Articles

[PageSpeed] 6

```Use two cells.

Say we have a formula in A1:

=SUM(A2:A100)

Sometimes we would like to override the formula with a fixed value and still
have the formula available.

Use B1 for the fixed value and in A1:

=IF(ISBLANK(B1),SUM(A2:A100),B1)
--
Gary''s Student - gsnu200909

"MsBeverlee" wrote:

> Hello.  I am creating an Expense Report worksheet and have created a simple
> formula that will calculate mileage based on total miles.  Below is my
> worksheet data.
>
>         A                              B                         C
>             D
> 1  Expense Type            Acct. Code        Total Miles	       Amount
> 2  Airfare	                   11111
>   \$250.00
> 3  Mileage	                   22222                     20
>  \$10.00
>
> I am trying to figure out a way to create a conditional formula so that IF
> Expense Type is "Mileage", data in "Total Miles" must not be blank, and the
> "Amount" will automatically calculate based on the formula "C3*.50".
>
> Additionally, I want to be able to enter an Amount in Column D without
> overwriting the underlying formula.  For example, say in A2 I enter Expense
> Type "Airfare" and then enter the total amount of \$250.00 Column D.  Once I
> enter data in Column D, I overwrite the formula.  If I need to go back and
> change the Expense Type to "Mileage", Column D won't automatically calculate
> anymore.  By typing data into this cell, I have essentially deleted the
> formula.  Is there a way to maintain the underlying conditional formula while
> at the same allowing me to manually enter data in that cell without losing
> the formula?
> 0  Utf Similar Artilces: