Hi,
I have an hopefully interesting request for someone familiar with data-merging in Excel 2013.
I just got my first job and I'm trying to create some new user-friendly spreadsheets for them to use.
The data I am working with is input from customers on their estimated demand for our products in the next 12 months.
So I have an odbc link (which I know very little of) that brings up a ~6000 row data set into a table upon clicking "Refresh All". This data set changes in row numbers every time a change is made to the external database (which happens all the time).
This dynamic table only brings up the estimates from current month and 12 months forward (i.e. apr 2015 to apr 2016). I would like to "save" these estimates at the end of each month and be able to compare what the customers tell us from month to month.
I have started to build a seperate static data table where I just copy in the values at the end of each month and add a column saying when the data was forecasted. This works great for comparing data for past months, but I can't find a good way to also bring in the current (dynamic) data in the pivot.
I've looked into table connections, but none of my columns in the raw imported data contains unique values. The only unique combination is "Customer name+Product name+Demanded in month" combined.
There may be other easier way of working with this static and dynamic data together. Maybe something as simple as having the dynamic table in the row below the static data or something like this. I'm pretty new to Excel and struggling a lot to figure this out.
I've attached a sample file that should illustrate the issue. I would be eternally grateful if someone could help me out.
https://www.sendspace.com/file/getb3g (sorry if the download site is spammy)
Thanks in advance!
I have an hopefully interesting request for someone familiar with data-merging in Excel 2013.
I just got my first job and I'm trying to create some new user-friendly spreadsheets for them to use.
The data I am working with is input from customers on their estimated demand for our products in the next 12 months.
So I have an odbc link (which I know very little of) that brings up a ~6000 row data set into a table upon clicking "Refresh All". This data set changes in row numbers every time a change is made to the external database (which happens all the time).
This dynamic table only brings up the estimates from current month and 12 months forward (i.e. apr 2015 to apr 2016). I would like to "save" these estimates at the end of each month and be able to compare what the customers tell us from month to month.
I have started to build a seperate static data table where I just copy in the values at the end of each month and add a column saying when the data was forecasted. This works great for comparing data for past months, but I can't find a good way to also bring in the current (dynamic) data in the pivot.
I've looked into table connections, but none of my columns in the raw imported data contains unique values. The only unique combination is "Customer name+Product name+Demanded in month" combined.
There may be other easier way of working with this static and dynamic data together. Maybe something as simple as having the dynamic table in the row below the static data or something like this. I'm pretty new to Excel and struggling a lot to figure this out.
I've attached a sample file that should illustrate the issue. I would be eternally grateful if someone could help me out.
https://www.sendspace.com/file/getb3g (sorry if the download site is spammy)
Thanks in advance!