Order form

 I created a product list using excel. The product list requires a number of formulas which will be explained in this post.

First, The Lists:

1- on the product sheet, we have created two lists, one for the product name called (ProductList) and the other one for all the price and the name called (ProductLookup).

2- On the consumer sheet, we created two lists as well, one for the consumer name called (consumerList) and the other one for the consumer name, address, postcode, and the city called (consumerLookup).

After that, the lists created were used to give the option to choose the product, as shown below:

The product list

The same thing was done to the consumer list; it was used to allow us to chose the consumer name, as shown below:

The consumer list

second, The formulas used to complete the product list:

1- I used a formula to show the price associated with the product chosen; thus, if there was a product, it would show its price; otherwise, it will show nothing. The formula is written above, the IF function followed by productLookup list because it is connected to the product name. Therefore, if a product was chosen from the productList, the price of the product will appear. 

For example, when the chocolate chip cookies product was chosen, its price appeared automatically, as shown in 1.1.

1.1 

2- To show the total, we need to multiply the price by the quantity. The formula should be input as =C10*D10. However, as what was done on the price column, we want the total to appear if the product was chosen. Thus, the IF function was used again.

3-Finally, to show the address and city of the shiping, the IF function was used again, but this time the consumerLookup list was used since it is connected to the consumer's name. Thus, since the IF function was used, the address and postcode and the city will not appear unless the consumer name was chosen, as shown in 1.2.

1.2

The Product list will show the shipping address, the quantity, and the total. 

The Product list