Trying to create a dynamic sales report

Question

Hi I am trying to create a dynamic sales report, so that I dont have to manually enter so much.&nbsp;1) I will have a master sheet where I enter the sale information in each row.&nbsp;example of sheet 1:"Order #"&nbsp; &nbsp; &nbsp;"Item Name"&nbsp; &nbsp; &nbsp;"# of Items purchased"&nbsp; &nbsp; &nbsp;"Price paid per Item"&nbsp; &nbsp; &nbsp; (etc.)&nbsp; &nbsp; 001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Widget 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$2.99&nbsp; &nbsp; 002&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Widget 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$5.99&nbsp; &nbsp; 003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Widget 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$1.99&nbsp; &nbsp; 004&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Widget 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$2.99&nbsp; &nbsp; 005&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Widget 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$3.99(etc.)2) I want the 2nd sheet to automatically pull from the master sheet, the orders (as they are being entered).&nbsp; This 2nd sheet will have sections of rows dedicated to each Widget #.&nbsp;example of sheet 2:WIDGET 1"Order #"&nbsp; &nbsp; &nbsp;"Item Name"&nbsp; &nbsp; &nbsp;"# of Items purchased"&nbsp; &nbsp; &nbsp;"Price paid per Item"&nbsp; &nbsp; &nbsp; (etc.)&nbsp; &nbsp; 001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Widget 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$2.99&nbsp; &nbsp; 004&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Widget 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$2.99&nbsp;Total:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$5.98&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(etc.)&nbsp;WIDGET 2"Order #"&nbsp; &nbsp; &nbsp;"Item Name"&nbsp; &nbsp; &nbsp;"# of Items purchased"&nbsp; &nbsp; &nbsp;"Price paid per Item"&nbsp; &nbsp; &nbsp; (etc.)&nbsp; &nbsp; 003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Widget 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$1.99&nbsp; &nbsp; 005&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Widget 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$3.99Total:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;11&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$5.98&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(etc.)&nbsp;a)&nbsp; I want the 2nd sheet to dynamically and automatically know when a new row in the master 1st sheet is entered by a user.&nbsp;b)&nbsp; I want the 2nd sheet to&nbsp;dynamically and automatically recognize which widget # has been sold when a new row in the master 1st sheet is entered by a user.&nbsp;c)&nbsp; I want the 2nd sheet to&nbsp;dynamically and automatically go to the right widget section in the 2nd sheet.&nbsp; Then insert a new row under the last row of that widget section and enter the data in that new row from the new order on the 1st master sheet.&nbsp;Currently I am having to manually do all of this.&nbsp; It was ok when I was getting one order a day, but when you are getting 20+ orders a day, this is taking forever.I so much appreciate any help!&nbsp;

lorenzo · Answer

Hi&nbsp;jaekimva&nbsp;&nbsp;2 options attached:Power Pivot reportPower Query example. Currently configured to refresh when the file opens, then every 3 minutes&nbsp;Useless to mention that option #1 is the easiest one

nikolinode · Answer

&nbsp;Here are some links with templates in this direction... you can then expand it yourself according to your wishes.&nbsp;Sales report - Microsoft templates - Office 365Monitor your customers and products with this accessible sales report for Excel. Each quarter is broken out within this sales report template. Create a professional sales report that includes space for product descriptions and customer names.&nbsp;Sales report templatesUse these templates to track and report your weekly, monthly, and quarterly sales.&nbsp;Hope I could help you with these links.&nbsp;NikolinoDEI know I don't know anything (Socrates)jaekimva

jaekimva · Answer

Hi thank you, but it doesnt look like what I need.

jaekimva · Answer

Thank you! I will check it out.

Forum Discussion

Trying to create a dynamic sales report

4 Replies

Resources