Auto-generated Form with Fixed Labels

[Qaswarah]

Hello guys.

I'm kinda new to Excel, and I need your help.


So, I need to instruct a form-like sheet in Excel to be as follows:

Product	Quantity	Name	Code	Price
	##	"product A"	##	$#,###
	##	"product B"	##	$###

<tbody>
</tbody>

The current situation:

• I have the products already listed alongside their prespective codes in Sheet1, and I already established a successful formula to calculate the pricing and so on...

---

The required results:

• I want a formula to lookup in a certain row (starting from a certain column and to infinity) to check for the entered values, and return them into the table in Sheet2 in the column titled quantity (each quantity in a separate row if possible).
• In the column titled Name I need to list the corresponding product-names each a cell after its own needed quantity.
• Obviously, I need to enlist the product-codes for the corresponding products into the column titled Code.
• And clearly, the fourth column titled Price is for the corresponding product-prices each in front of each's product code.

---

What I have in mind:

• I thought of using an if statement different in each column, i.e. in the "Quantity" column and is changed correspondingly for the other columns, structured as follows:
  • if the selected range has an entered value; return the values found (each in a separate row in the same column, if no value is found it would return "blank" or a dash, i.e. "-";
  • the other three columns would just have different range matrices but it would be the same structure.

---

I think the problem is just finding the structure of the needed equation for one of the columns or even for one cell in each column; as each column might have at most 20 rows that can be easily edited whenever required.


Thanks for whoever can help.