Forum Discussion
Excel 2016 how to autofill data entries based on a reference sheet
The workbook you would be building is the one with the pivot tables. The third table (or the same information held within the data model for Power Query) would be there and would be refreshed monthly (all the previously defined steps would be repeated automatically). New data might replace the old or you could choose to append it.
One objective would be to get hold of the sales data from as near the source as you are allowed to go (in the current workbook, in another workbook, within an exported text file, using an SQL query to interrogate a corporate database). All the data manipulation would be within PQ so that you never need to touch the data again. The category and brand data could be read from a separate, largely static file or could be a table in the destination workbook.
p.s. By 'refresh' I simply mean click the 'Refresh All' button on the Data ribbon tab.
p.p.s. I have tried to append a file as the 'starter for one'. It contains the three tables on a single sheet but that would not be the longer-term aim. Open the queries from the panel on the right and step through the edits to see the data at each stage.
=VLOOKUP(B2,
Sheet2!$A:$D,
COLUMN(C$1),0)
Instead of Sheet2!$A:$D, I suggest that you define a name for that dynamic range. The defined name might be "Products", with this formula:
=Sheet2!$A$2:INDEX(Sheet2!$A:$D,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!$1:$1))
Thereafter, you may instead use this formula in Sheet1!G2, copied down rows and across to H2:
=VLOOKUP(B2,
Products,
COLUMN(C$1),0)
