Transform a Product Stock table into Invoice-like table automatically (generating Invoice)

Question

apologies for such a bad title. First time creating thread here.

So i have this master table that includes columns:

• SKU
• Price per-item
• Opening Stocks
• Closing Stocks
• [and every Customers Name]

For the rows of Customers Name's columns, it will contain the quantity of the stuff they're ordering.

Here are the picture of the table: Master Table

Here are the links of the Google Sheets: https://docs.google.com/spreadsheets/d/1NDnJrSqM4OkG03r7P2lScafOZBzxs9nV4Nluqmv0YiU/edit?usp=sharing

The main goal of these problem is to create invoice for each one of the Customers Name.

Here are the image of the invoice that i created manually: Invoice (also included on the Google sheets link above)

I'm expecting to have a formula that could:

1. Generate the invoice automatically by double clicking the column of the Recap's sheets [if possible :D]
2. Generate the SKU, Quantity, Price/Item, Total in the Invoice automatically as soon as i put the Customer Name on the Invoice field

Answer 1

Here's one approach for your point 2. Change the name in B4_Cell to test it out:

=let(Σ,indirect("Recap!A1:"&rows(Recap!A:A)),Λ,choosecols(Σ,xmatch(B4,chooserows(Σ,1))),
     sort(filter({Recap!A:A,Λ,Recap!B:B,Λ*Recap!B:B},--Λ)))

• within the Invoice tab you seem to have ~18 blank rows between headers(row_13) and SUBTOTAL (row_32); so in a scenario where the formula data is more than 18 rows it will end up throwing an error