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?

Thanks for your help!
0
Utf
1/16/2010 12:39:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

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?
> 
> Thanks for your help!
0
Utf
1/16/2010 2:09:01 PM
Reply:

Similar Artilces: