auto update of lists monthly

[nancebloggs]

Hi all,

I have a spreadsheet with several workbooks which I need to update monthly. They are reports on different supplier sales figures & items. Each supplier has their own worksheet where I usually copy & paste the items sold each month, the number of items sold and the gross amount. 

Is there a way I can get each worksheet to update the items sold each month for each suppliers category? 

I've included a screenshot of one report as well as the master worksheet. 

The columns I'd like updated in the supplier sheet would be columns B, C & E which feed from columns B, E & L from the master worksheet. Thanks.

thanks. 

[#2 · Accepted solution]

Hi @nancebloggs 

there are several options I can think of, depending on the Excel version that you use.

If you use Microsoft 365/Office 365, you could take the new FILTER function to get dynamically the relevant data from the master sheet into each supplier sheet.

Another option which works also with Excel 2016 - 2021 is Power Query. Load the data from your master sheet into Power Query where you can apply filters by supplier. And then load the results into the supplier sheet.

[#3]

@Martin_Weiss thanks so much! Filter function worked!!