Forum Discussion

nancebloggs's avatar
nancebloggs
Copper Contributor
Jul 14, 2022
Solved

auto update of lists monthly

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. 

excel
Formulas and Functions
  • Martin_Weiss's avatar
    Martin_Weiss
    Jul 18, 2022

    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.

     

2 Replies

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Jul 18, 2022

    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.

     

Resources