A smart table that autofills row values regardless of the input cell

**Damiangjy** · 07-20-2017, 04:46 AM

Hi all,

I am looking for a method to get excel to automatically calculate the values for a row of cells regardless of where I input my data.

For example, say I have a 1 x 3 table calculating the total cost of something.

Column 1 specifies the number of objects, column 2 specifies the unit price, and column 3 specifies the total cost.

Number	Price	Total
2	$3	$6 (calculated)

Normally, I would put a formula in the third column: "=A1*B1". I would input the number of objects and the unit price, and have the total cost calculated automatically by the formula.

However, I want to make it such that I can input data not restricted to the number and the unit price. Sometimes, I may only have the total cost and the number of units, or the total cost and the unit price, and I want to find out the missing column. Is there any way that I can program excel such that I can get it to auto fill the table regardless of what data I input to it?

Number	Price	Total
20 (calculated)	$4	$80

**Special-K** · 07-20-2017, 05:28 AM

A cell can contian either a value or a formula. But if you type a value into a cell that contains a formula, the formula will get deleted.
Same goes for entering a formula in a cell that already contains a value.

You could do it via VBA or this way:

Enter your data in columns D E and F with your actual results in columns A B and C
Once you have entered two values in D E F the third can be calculated

in A1
=IF(COUNTA(D1:F1)<>2,"",IF(D1="",F1/E1,D1))
in B1
=IF(COUNTA(D1:F1)<>2,"",IF(E1="",F1/D1,E1))
in C1
=IF(COUNTA(D1:F1)<>2,"",IF(F1<>"",F1,D1*E1))
and copy down the columns

Now when you enter two values in columns D E and F, columns A B and C will calculate the values as you've described.