Google Sheets Product Sales Calculator

Create a Google Sheet with two sheets:

Sheet 1: "ProductList" (For storing product details)
This sheet will have three columns:

Product Name – List of all products

MRP Price – Maximum Retail Price of each product

My Price – The price at which I buy the product

Example Data:

Product Name MRP Price My Price
Product A ₹1000 ₹800
Product B ₹2000 ₹1800
Product C ₹3000 ₹2700
Sheet 2: "SalesRecord" (For recording sales transactions)
This sheet will have six columns:

Date – The date of the sale

Customer Name – The name of the customer

Product Selection – A dropdown that includes all products from "ProductList"

MRP Price – Automatically fetched based on the selected product

My Price – Automatically fetched from "ProductList" based on the selected product

Retail Price – Manually entered selling price

Profit – Automatically calculated as Retail Price - My Price

How it Works:
Dropdown for Product Selection:

In the "Product Selection" column of SalesRecord, create a dropdown that pulls product names from ProductList.

Auto-fetch MRP Price:

Use VLOOKUP to get the MRP Price from ProductList.

Example formula for D2 (MRP Price column in SalesRecord):

excel
Copy
Edit
=IF(C2="", "", VLOOKUP(C2, ProductList!A:C, 2, FALSE))
Auto-fetch My Price:

Use VLOOKUP to get "My Price" from ProductList.

Example formula for E2 (My Price column in SalesRecord):

excel
Copy
Edit
=IF(C2="", "", VLOOKUP(C2, ProductList!A:C, 3, FALSE))
Calculate Profit Automatically:

Formula for G2 (Profit column in SalesRecord):

excel
Copy
Edit
=IF(OR(E2="", F2=""), "", F2 - E2)
Final Flow:
In "ProductList", add new products with their MRP and My Price.

In "SalesRecord", select a product from the dropdown.

MRP Price & My Price will auto-fill.

Enter the Retail Price manually.

Profit will be automatically calculated.