Continually AutoPopulate Tables in Excel

[akille]

This might be hard to explain, so bear with me.



I have a data set that will automatically add information as it becomes available (Using the FRED add in to get economic data imported to excel), I have charts and graphs that will automatically update, however I need to make a table using, for example, only the most recent 10 periods. It's easy enough to make a chart using the data that I have, however, I need the chart to automatically update and only keep the most recent 10 periods. For example, if I currently have data from 2005-2015, that's great, HOWEVER, when data becomes available for 2016, I then want my chart to automatically populate with values from 2006-2016, pushing 2005 out of the table.



My data populates more often than just once a year, so it would be incredibly time consuming to remake every table every time new data was imported.

 

[Zack Barresse]

Hi, and welcome to the board!

Why don't you create a Power Query (now known as Get & Transform) based on your Table, set you conditions to filter based on date, then use the output for your chart source? All you would need to do then is refresh the query.

 

[akille]

Unfortunately, it looks like the Power Query tool is an add-in, and on this computer I can't install it. Also, I made a mistake in my original post. I have charts that are automatically populating and updating, what I need are tables. Here's what I meant to say---

"It's easy enough to make a chart using the data that I have, however, I need the TABLES to automatically update and only keep the most recent 10 periods. For example, if I currently have data from 2005-2015, that's great, HOWEVER, when data becomes available (and as soon as it is available, it will automatically download into my spreadsheet) for 2016, I then want my TABLE to automatically populate with values from 2006-2016, pushing 2005 out of the table."

 

[Zack Barresse]

What version of Excel are you using? Power Query is included in 2016. Since you didn't mention, I assumed you had the latest version of Office. Please clarify.

I understand your need for Tables. This is what Power Query outputs to. If you can't use Power Query we can do this with VBA, however we would need to know what your data structure looks like, where the data resides and where you want it output to. I'm assuming you want this functionality tied to a button or some such, and when pressed it will remove the destination data and refresh it?