Pivot table from Multiple sheets

[mark hansen]

(Using Excel 2013)
I'm creating dashboard to look at one year, plus the current years data.

We have a system that we are pulling information from that only allows data to be pulled for up to 12 months. So we only get a year's worth of data at one time.

We would like to look at all of 2019 and 2020 year to date. So we will pull all of 2019 one time and place it in a table. Then weekly, we will pull from 1 Jan 2020 to the current date and place in a table. Next week, we again pull 1 Jan 2020 to the current and REPLACE the previous weeks data pull in the 2020 table.

I've been looking a various website and they speak about a data model and how to relate tables using a primary key which makes a one to many relationship. I'm not sure if I want to do that because both tables will have the same columns. The 2020 data pull is just a continuation of the previous year's information.

Should I not use two separate tables and read in the data and append it to the 2019 table? (the 2019 data will have a specific and static numbers of rows, as it won't change as of the end of the year 2019). With code, I could delete the 2020 data before I re-import the 2020 data.

Thanks for any ideas.
Mark

Or should I continue looking regarding how to work with tables that contain different data, but the columns are the same?

 

[sandy666]

try Alt+D+P

 

[mark hansen]

Thanks, I'll give that a try

 

[sandy666]

You are welcome

Consolidate multiple worksheets into one PivotTable - Microsoft Support

Consolidating data is a useful way to combine data from different sources into one report.

support.office.com