Skip to Content
avatar image
Former Member

Periodical Change in Data Source

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 12, 2017 at 09:39 PM

    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

    Add comment
    10|10000 characters needed 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...

  • Apr 13, 2017 at 08:13 AM

    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

    Add comment
    10|10000 characters needed 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...