Forum Discussion
A Little Help from my Friends
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.