Skip to Content
0

Periodical Change in Data Source

Apr 12, 2017 at 04:15 PM

78

avatar image
Former Member

Every month I run a margin report based on two data sources

1. Sales Data (excel file)

2. Costing Sheet (excel file)

The Sales Data changes every month and the Costing Sheet is the bases for the margin calculation.

The problem:

The costing sheet suffers contant alterations month in month out. As a result, I have to change the data source for the costing sheet in order to use the latest Costing Sheet (Jan 2017 Costing Sheet, Feb 2017 Costing Sheet, etc). This requires me to change all the elements of the connection to that new data (data source location and all the formula links).

Any easier way to deal with this issue?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Brian Dong Apr 12, 2017 at 09:39 PM
0

Hi,

There's no real easy way to deal with this if the structure of your datasource changes. One way to minimize issues would be to use an ETL to import the Excel files into a database. The tables in the database remain static but you would still have to tweak the ETL so it can use the updated spreadsheets.

This really just adds another layer and pushes your current work into the ETL. The obvious answer would be to not allow changes to the spreadsheet but that's not a realistic solution I'm guessing.

Brian

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank you Brian. You confirmed what I suspected all along... Unfortunately the Costing Sheet does change over time, and in order to keep historic figures I must keep previous used data source unchanged and use newer ones for the most current exercise. This a lot of redundant work...

0
Ian Waterman Apr 13, 2017 at 08:13 AM
0

You could also change your process whereby you add a date field to spread sheet eg Jan2017.

You can then have a static spreadsheet with same name and then just append data to bottom of static spreadsheet. Or simply over write existing data. This will save you changing connection details of report each nmonth and allows you to have historical data too if you append each months data.

Ian

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank you Ian... thought of that too... I will end up with a hugh spreadsheet after a few years, though... Then again, given that Excel can handle rows in the millions and columns in the thousands I suppose it won't be an issue...

0