Create a dynamic product invoice in Excel

Question

I'm trying to create a dynamic invoice in Excel. The dynamic part is that the user can pick which store the invoice is for from a list. I'm trying to get the available items to populate after the store is selected.

On one sheet of my workbook I have all different tables that define the stores, products, and what products each store carries (intersection records between the two). In my example "Corner Market" carries: all 4 products, but "Tim's Hardware" only stocks 1/2" washers and Drill Bit Sets.

I would like to generate a dynamic table like that in N5 and N23 in the linked screenshot whenever the user picks the proper store (N3 and N21). So the table should change as the Store Name value changes. (I'm only interested in generating one invoice but included 2 in the screenshot as an example.) The user can then enter the quantity values to generate each line item price and the invoice total amount.

I'm looking for advice on the best way to accomplish something like this. If possible I'm trying to avoid using VBA macros. I'm guessing a pivot table may be the solution, or maybe something with the data relationships (which I'm not too familiar with).

Screenshot: https://i.sstatic.net/ICZDW.png

Any help is appreciated - thanks in advance!

Answer 1

You can add the 3 tables into Data Model of Power Pivot.

But please note, Power Pivot can be found in the Office products mentiond in 'Where is Power Pivot?'. The relationships between them are as following image.

The Store ID in table 2 and Product ID in table 3 are primary key fields.

Then you can create a Power Pivot Table.

The Store Name can be a filter field. Product Name can be a row field and the Unit Price can be a value field.

But the Quantity and Price columns can be written in the next columns.

Under the Price title, you can enter the formula =IF(P12="","",GETPIVOTDATA("[Measures].[Sum of Unit Price]",$N$11,"[Table3].[Product Name]","[Table3].[Product Name].&[1/2"" Washer]")*P12), please drop the formula down to cover a large enough range of data. Such as I select the range from Q12:Q21 to get the price values. The part of GETPIVOTDATA("[Measures].[Sum of Unit Price]",$N$11,"[Table3].[Product Name]","[Table3].[Product Name].&[1/2"" Washer]") can be use mouse to choose O12 instead.

For Total, you can enter the formula =IF(SUM(Q12:Q21)>0,SUM(Q12:Q21),"").