• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Importing Data

P

PaulFogel123

Member
Hello Everyone,
I have an Excel model that relies upon a monthly refresh of data. The data would be imported into a separate sheet in my model and arrive in the form of a very large database extracted to Excel. I have experimented with creating a Pivot Table from this download, and then using GETPIVOTDATA formulas to get the figures I need. The problem: the Excel data extract will come to me as a new file each month. This means the file reference to which the GETPIVOTDATA formulas previously referred would have to be changed each month. I'd have to do a lot of copy>paste or edit>replace, and that's going to be a hassle with 50 entities to deal with.

Is there a more direct way? The Data ribbon allows one to import from an external database but I don't know how to use it. Is that a possibility?

Paul
 
Paul

Pivot tables summarize data

So this can all be done using formulas directly on the Source Data

If you were to paste a copy of the data we could advise specific alternatives
If the data is proprietary, simply multiply all the values by a random number like 1.23456 and changes names to ABC etc
 
Here is a small sample of both files. The database file will come across in the format shown in Sample Database File.xlsx. Column U, "Mapping," is where the data needs to be summarized and get loaded into the model. For example, the total of Paid Salaries would be uploaded for each month into my model (Model.xlsx) with the corresponding dates. However, the dates/columns won't match, rather, they'll be in different positions for the two files, as you can see. I have been using array formulas and SUMPRODUCT to find a match between the date in the source file and the Model previously, but I don't think it will work here, as Paid Salaries, for example, is not a single row, but many.

Thanks so much for looking at this.
 

Attachments

Pure genius. When I get a new file each month, I think that if it is laid out identically, all I would need to do is rename it to replace the last month's file, make sure it has the same path and location, then open Model.xlsx, and let it refresh. Does that sound about right?

Perhaps you could explain the formula in English so I can understand it better.
 
I copied the Monthly report worksheet into the Model file, I didn't link it externally

I'd be more likely to
1. Clear the upload sheet
2. import the new report into an Upload sheet each month
That way you can use named formula to automatically pickup the data
It can be automated if required.
 
I have a related question, if that's alright.
One of the data fields is Company (column B). Another is Fiscal Year in column A. Suppose I wanted to distinguish between them. For example, one of my models wants to pick up Paid Salaries for Company 100 for 2014, another model needs Paid Salaries for Company 101 for 2013 only. How could that be done?
Paul
 
This technique is based on my post here
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/
which explains how the formula works

You can add as many fields as you want so long as they are all the correct size
When working with 2D ranges you need to ensure that the Columns are all kept together within brackets and same for Rows

Best to post a sample file with suitable data for a more specific answer
 
In this sample file, I now have Company 100 and Company 200, and 2013 and 2014 data for each. Would you be so kind as to fill in the formulas in Historical Actuals DB for 2013 and 2014?
 

Attachments

I wonder if I can impose upon you for one final tweak--
the dates on Sheet1 of the attached will come across exactly like this:
CYPAMT1 for Jan, CYPAMT2 for Feb, and so on, all the way to CYPYTD for year-to-date, which I don't care about. The dates on Historical Actuals DB must remain as is. I cannot figure out a way to make the date match of the SUMPRODUCT formula work without overwriting CYPAMT1, for example, to 01/31/14--and even that won't work, since CYPAMT1 could also be Jan 2013.

Would you work your magic one more time?
 

Attachments

You must log in or register to reply here.
Back
Top