Forum Discussion
Macro to change sheet displayed
Oh, my. I strongly (STRONGLY!!) recommend that you combine all those monthly sheets into a single, ongoing database. Just add a column that contains the effective date of the data on that row. From such a re-designed database--unless I'm missing something--you'll be able to do all the analyses you want to do, certainly including year-over-year changes in each month. Are you open to such a change (or in a position to do so)?
That's what I WANT to do. As with other visualising tools it isn't necessary. However it needs to be something that can be easily viewed still hence the 2 sheets per month. Thats why I had the request to pull to and clear one singular sheet.
But yes the other way I had in mind as a last resort and see how it goes down is to combine it all and just filter by month as the key is to be comparing each month's history.
- mathetes
Ah, I recall now. Previously when I've looked at doing this, it messes with my formulas for comparisons to the previous month. Is there a way I can alter them to adapt to filtering??Previously when I've looked at doing this, it messes with my formulas for comparisons to the previous month. Is there a way I can alter them to adapt to filtering??
Tell you what. Why don't you write out all of the current goals or objectives for this workbook. What are the outcomes, results, outputs (however you want to say it, whichever term is most appropriate)?
Working with a single, well-designed database is far and away the most solid foundation you can have for what would appear to be your situation. Excel has marvelous tools to parse data, enabling comparisons like
- month-over-same-month-in-prior-year
- month-over-prior-month
- quarter-over-quarter
- etc
The Pivot Table, for one, would enable you to do quite a bit of that. But also FILTER, functions like DGET (one of several data table extraction functions)
But let's begin with a full description of what you need in the way of output from all this input.
The aim of this workbook is to compare the status and performances metrics of each centre on a month-over-same-month-in-prior-year basis, for example how full each centre is, how much income is generated and if businesses are from the target post code. Essentially it exists to display the data you currently see. I will ALSO be creating PowerBI visuals from this (hence I would much prefer a database) so it had to be suited for both, hence one table per sheet for PowerBI.
I have created formulas to display most recent data but they don't work on a filtering basis. My excel knowledge is limited as I do most visualising on Power BI so having them adapt to filtering goes a little further than what I have learnt.
Each centre has its own workbook but I want to change this to make it easier to access as well as edit, this can easily be done with a hidden column. I have been told this isn't required but demoing it working will go down very well so I have no concerns with that as the team are definitely looking to develop the data side
I completely agree with having the database. In previous jobs working on projects I've always worked with a database to then visualise from there, coming into this there is no database that spreadsheets are then created from which has made things unnecessarily convulated.