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!

Overwriting a cell with a formula without deleting the formula
 

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!