I have WORKSHEET1 with all the sales. And WORKSHEET2 that has 2 tables "Wholesale Price", and "Retail Price". And I have WORKSHEET3 that sums all SALE amounts from column H on WORKSHEET1, if it meets the criteria that I type into cell A1 (the category), and the date range that I type into cell B2 (beginning date), and B3 (ending date).
This is as far as I have gotten.
What I need now, is for a dynamic table on WORKSHEET3 that populates itself with more information about the sales (with the same criteria), and not just the total. It needs to find all instances of the category that I type into cell A1, that also falls between the date range that I type into cell B2 and B3. Then for it to list each ITEM that it finds (grouped together in 1 row), and to reference the sum of each ITEM with the tables on WORKSHEET2 (which have ITEM name, PRICE, and UNIT), dividing by it by its retail price in order to figure out its unit, and then multiplying that with its wholesale price. This will populate 5 columns on the table (ITEM NAME : WHOLESALE PRICE : QUANTITY : UNIT : TOTAL
And to make it extra difficult, the retail and wholesale price lists on WORKSHEET2 have a short-name, and will not be an exact match to the ITEM NAME on worksheet1. The way our point of sale app records information, we've had to create unique item names for each vendor, so we might have 20 vendors (category) who all sell bananas among other things, but in the "ITEM NAME" column it will be recorded with a unique abbreviation as their name at the beginning... ie. "(CG) Banana", or "(BRF) Banana". Is this still possible?
This is the formula I am using to get where I have.
=SUMIFS(WORKSHEET1!H:H,WORKSHEET1!A:A,">="&WORKSHEET3!B2,WORKSHEET1!A:A,"<="&WORKSHEET3!B3,WORKSHEET1!F:F,A1)
I hope this makes sense.
Bookmarks