Updating data in excel automatically
Every time when you add new data in the source sheet you need to update the source range for the pivot table before you refresh your pivot table.
Now, just imagine if you add data to your source sheet every day you have to update source range every day.
These grey cells can now be linked into the rest of your model like normal inputs.
A completed excel file containing this and other web queries examples can be downloaded from: This process can also be applied to query tables that link to access databases.
Some data fields may be text formatted or contain currency signs, so some processing may be required.
If you use pivot tables in your work frequently, I am sure you face the problem to update its source data range manually.
The table below provides some examples of inputs for an M&A mining transaction. In this example I’m going to import Australian Bond data.
I have mentioned cell A1 as the starting point and then without mentioning rows and columns, I have specified the height and width of the range by using COUNTA Function. Range(Start Point, Cells(Down Cell, Last Col)) New Range = Data_Sheet.
COUNTA function will count the cells with values from column A and row 1 and tell offset to expand it’s height and width accordingly.
Fortunately, with a little know-how, you can get what you want.
When you can't, you might have to consider restricting the data.