Excel updating separate spreadsheets
Supposing you have created a chart to track the daily sales based on a range of data in your workbook.But you need to change or edit the number of the data every day, in this case, you have to update the chart manually so it includes the new data.See screenshots: The score of first term: The score of second term: The score of third term: The score of fourth term: Now I can use the Consolidate function to summarize the data from multiple worksheets into single worksheet, please do as follows: 1.Create a new workbook that you want to put the consolidated data, then click Data Consolidate, see screenshot: 2.In the Consolidate dialog box, select Sum (as we want to show sum of values for consolidation ) from Function option, then click button from Reference to select the range that you want to summarize, and then click Add button to add the range reference to the All references list box. Using the same way to add other ranges to the All references list box.If your data contains labels for top row and left column, please check Top row and Left column under Use labels in.And then click Finish button, and you will be asked if you want to save this scenario.
But sometimes, you don’t want to change the range to table, and the above method is not available in Excel 2003 or earlier version.
Here I can introduce you a complex dynamic formula method. First, you need to create a defined name and a dynamic formula for each column.
Click Formulas Note: In the above formulas, the OFFSET function refers to the first data point, and the COUNTA refers to the entire column of data. After defining the names and formulas for each column data, then right click any column in your chart, and choose Select Data, see screenshot: 5.
When you want to use this scenario, you just only need to click Scenario button to choose the name of the scenario you need on step2 wizard.
And then all the data from the worksheets has been summarized into one worksheet of a new workbook.