Forum Discussion
A Little Help from my Friends
I am an advanced (expert) in Excel. I've used it for over 20 years everyday and have thousands of education hours under my belt. I would like to get some ideas on how some of you would approach this complex situation I have. I have already created three different methods but each have too many draw backs or failure points. With that said, I'll try to be brief but detailed enough for your thoughts.
I have a file for which I'll call the Master-File it has a list of parts, suppliers, prices by month with Forecast and Actual. Current and Future months are forecast, previous months are actuals.
I have 6 people that send me, what I'll refer to as Sub-Files. These subfiles contain the same headers as the Master-File.
In the Master-File previous months data cannot change. (Ex. If reporting was done today 7/14 Actuals for June would be imported as Actuals and the remaining July thru December would be Forecast updated with the latest forecast data, which is already included in the sub-files). Months are listed left to right in columns not rows.
Also, those sub-files can contain new parts that are required to be imported into the Master-File.
My first solution was to use Power Query and import the Sub-files each month from a folder into the Master-file. Problem with that was, data from previous months would change in some of the Sub-Files and would over-write the data. (This data can't change once locked in).
My second solution was to utilize a Unique ID for each record where I could easily identify new parts first and import that data by manually copying and pasting the data. Then sort the data using a match formula so the records in the Sub-file matched the Master-file and then manually copy and paste in the Actual and Forecast data. Some data was not correctly matched, so I started using XLOOKUP to import the data in the Actual and Forecast. Then, copy and paste as values.
With 6 different files the table would at times over-write the data in the Actual forecast fields causing errors. Even after turning off auto table formula update option. Plus, this was very time consuming.
Now I am starting over. I have some other ideas but if two heads are better than one then many heads are even better. I would love to hear your thoughts on how you would go about handling this process.
6 Replies
Before I mark that as a solution, I would first like to hear a few more ideas. Just in case....
I'm letting you and m_tarler deal with most of the substance of your inquiry. I do have one observation and question based on some prior experience of my own, which I'll describe. What caught my eye was the notion that some previous month's data does change but "isn't allowed to."
First, my prior experience and why this "does" but "isn't allowed" caught my eye. I had responsibility at one point some 30 years ago, before I retired, to report monthly headcount data to top management of a major corporation. The senior VP of human resources had "a thing" about consistency from month to month. If we reported a headcount of 1,234 for a given location for the end of June, then THAT PRECISE NUMBER had to be reported in July's headcount report, PERIOD! The trouble was that the reality wasn't caught in a timely basis; transactions for June--hirings, transfers, terminations, whatever--might be entered into the database early in July reflecting an actual change that had happened in June. So though 1,234 was an accurate reading of the database when first reported, it was no longer accurate for June by the time the next month rolled around.
I was more of a stickler for let's report actual facts, even if doing so requires slight adjustments to prior reports. That can be explained and understood--and, significantly, be less troublesome in the long run--building fiction on fiction in a never ending attempt to try to incorporate actual current history into an increasingly false picture of past reality.
Now, maybe that kind of "hanging on to a past number because once reported, we're stuck with it" mindset is not what you're dealing with. Maybe your reality really does freeze, once reported. But if you are in a situation like I describe, maybe dealing with volumes of inventory, or counts of sales calls--rather than headcount of employees--if you are being asked to juggle numbers in a way that really isn't accurate, because of delays in reports of transactions that affect previous months' numbers--I would encourage appealing to reason via a clear explanation of why and how last month's numbers NEED to be modified from time-to-time in order to be truly accurate.
In this sort of situation I would pull data from the 'sub-files'. then have reporting tabs for each month. The 'closing' of a month included a copy and paste-values of all the values for that month and hence 'locking' them in.
I like this idea. How would new items each month be added and accounted for?
I guess I could create a lookup of new items and then create a macro that automatically imports the new items to a new table row in the master file. Then once items are copied and pasted into each month the formulas in the master would import the data but then I would have to be sure to copy and paste formulas as values to close the actuals each month.
Which then technically, I could just continue what I was was doing. Automatically pulling in the data via power query and then I have to copy and paste new items (which I could create a macro to do this for me). Each month have an xlookup to import the data and when actuals are ready just copy and paste formulas as values. Which I could also create a macro to handle this as well.
I would avoid the macros. Without the sheet it is hard but basically I imagine using power query as you do now. let's say you pull all the data into a single table with dates and values then each month pulls all the data e.g. FILTER(data, (data[date]>=[thisMonth])*(data[date]<=EOMONTH([thisMonth],0)),"")
to make life easier I would have cell A1 or another fixed cell represent this month date
Alternatively if your PQ is already set up to all the sheets then your 'closing' could be copy and paste-values to a new sheet called Act-Jan and hide the original Projected-Jan and then who cares is that projected tab changes in the background.
