User Profile
MattyNt
Copper Contributor
Joined Oct 28, 2022
User Widgets
Recent Discussions
Formulas not interacting - COUNTIF looking at DATEDIF
Hello all, I have attached images of two formulas, the first looks at the start date of our customers and returns how long they have been with us. The second is on another sheet looking to determine how many of that column are under 2 years. I have tried all sorts. The initial column of data appears as "x.x" with the decimal number being the months, going up to 11. Is there a way to make this work? / better way to do this? I'm using Excel 365 as an extra note.514Views0likes1CommentRe: Macro to change sheet displayed
mathetes Ah, while preparing it to send I figured out the error. If you were interested I used the if_empty part in order to achieve this, so the formula now looks like this. =SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*(Table1[Month]=Presentation!F3)*(Table1[Site]=Presentation!A3),SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*(Table1[Site]=Presentation!A3)),2,-1)),2,-1) Again, thank you for your help. I was running through everything I learnt from the video and the answer struck me.953Views0likes2CommentsRe: Macro to change sheet displayed
mathetes The issue though is I want it to show all months only when the cell is blank. When it is populated I want it to filter. Hence the IF ISBLANK functions being utilised. So the first array in the formula does not have a month filter, but that array doesn't seem to work without it. The second array you have highlighted is how it will function when populated. I've watched the video through twice which has been very useful for understanding it more. But unless I'm missing something it doesn't answer this last query. I will attach the sheet shortly.4.3KViews0likes3CommentsRe: Macro to change sheet displayed
mathetes Thank you for this. I got it all working but now have one final feature to make work which I think I've worked out but can't seem to make work; When the cell for filtering by month is empty, it needs to show all months. I thought I could achieve this with the formula below but it comes back as #VALUE! . Through trial and error it seems like it needs the month filter, which it shouldn't? =IF(ISBLANK(F3),SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*Table1[Site]=Presentation!A3),2,-1),SORT(FILTER(Table1[[Date]:[No of Info Packs/ Responses sent]],(Table1[Year]>=E3)*(Table1[Month]=Presentation!F3)*(Table1[Site]=Presentation!A3)),2,-1)) F3 is the cell for filtering by month. I can attach the current version if necessary.4.3KViews0likes5CommentsRe: Macro to change sheet displayed
mathetes, Apologies for the very late reply, I've been meaning to come back but haven't had the time this month. Essentially I want to add an extra column to the database for the 'Site', of which there are 3. This will create 3 sets of data for each month. I wondered if the tables on 'presentation' could be filtered by site as well as year?4.4KViews0likes8CommentsRe: Macro to change sheet displayed
mathetes I have been getting to grips with this today and it is fantastic. Only initial changes I'm going to make are adjusting columns displayed on the presentation tab as well as an array of requires percentages I've managed to gather today. I also need to display increases/decreases which I think I may look to colour code for further ease of use, but I add this to the right hand table fairly easily as far as I can tell. One thing I wanted to ask was regarding the tables on the 'Presentation' tab. In order to add columns to these tables through the formulas, but not by increasing the range, how is this done? Do I just have multiple ranges in the formula? Haven't had the chance to test that yet. If you don't mind me asking, what else did you have in mind for what else can be done? I'm always keen to learn and improve. Lastly I'd like to say thank you very much for your help so far, it's much appreciated and I've learnt a lot.4.4KViews0likes10CommentsRe: Macro to change sheet displayed
mathetes I have completed this, here is the link to this new file https://drive.google.com/file/d/1NicDnp9LTl-y1QVCCf4-iBLUqT8AaYQr/view?usp=drivesdk I feel I should add regarding the purpose. This file is not for me to use during meetings, but someone else. I will not even be present for those meetings hence I want to make it as clean and easy to digest as I can.4.5KViews0likes12CommentsRe: Macro to change sheet displayed
mathetes 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.4.5KViews0likes14CommentsRe: Macro to change sheet displayed
mathetes 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.4.6KViews0likes17CommentsRe: Macro to change sheet displayed
mathetes Please find link to the file on Google drive below. I havent made any significant changes, this is how it is all set up and laid out. Certain fomulas are not yet complete but that won't impact anything. https://drive.google.com/file/d/1NBWpLNdZbH4j63AOBOjXsSrIjyxQqdWM/view?usp=drivesdk4.7KViews0likes19CommentsRe: Macro to change sheet displayed
mathetes Thanks for the reply! Yes I'll make a copy of the file without anything confidential and post here asap. My preference is to have a database that can then be visualised in powerbi but it's a request for it to be like this in spreadsheets unfortunately so it's out of my hands. But I have permission try to clean it up as best as possible for consumption4.7KViews0likes0CommentsMacro to change sheet displayed
Hi all, I have been given a workbook that contains plenty of tabs that even my navigation macro feels a little pointless as they're similarly named. Each tab is a month showing yearly data, with a 2nd row I've added to compare the 2 most recent years. However it feels poor to navigate, as it will be used in meetings. I had a thought but I can't quite figure it out. Would it be possible to have one sheet that can be used to display any of the other sheets upon selection or command? It would be a case of clearing its current contents and then pulling everything from another sheet, I think? Then the other sheets could be hidden. Is there a way of doing this? It's been a while since I've done excel. But I feel like this workbook can be a lot cleaner. My alternative is to take the time to combine all of the sheets and have a hidden column to filter by but I believe this would mess with my comparison functions... so changing displays would be preferable. Any help much appreciated!6.2KViews0likes22Comments
Recent Blog Articles
No content to show