Forum Discussion
Returning values based on Start Date / End Date in a given year
thank you! I have rolled out your formula to all clients and started the process of checking all contracts and see if they have correct status by year.
I have highlighted in yellow from lines 92 - 286 examples where should say 'Active' so I think just a couple more tweaks to the formula is needed.
The answer is yes to your question. If a date appears in that year, then its ACTIVE - UNLESS the end date is the 1st January - please can it be Null in those cases?
Hi peteryac60
Yes the two raw data tabs are downloaded directly from our CRM.
I need to combine them to get the output I need. Very, very hard and need several advanced formuli to get there! However, if Powerpivot can override this then even better.
Your point about using the data - hmmm I probably shouldn't have shared what I did! But as you already have it, as long as it is just you using it might be ok? Maybe delete all columns in raw data not used in the 'Direct' tab view, then it will be ok.
Hi Jenny
Not sure if this will help …..
I am in the process of researching and learning about an excel add in called power query.
What this is supposed to do (as I understand it so far) is to 'clean' your data and prepare it for subsequent use - most likely for use in power pivot (a far more powerful version of pivot table).
Typically you can take data that the user has created and format up easily into a useful format.
Having done the set up once that you can subsequently enter new data into the query and it will simply reformatted automatically. Sounds simple!
I was going to propose using some of your data from your most recent file as part of my test - but as this is proprietary information you may not wish me to.
Looking at your most recent file I see the enrolments tab and salesforce contracts tab - I assume these are downloaded from an IT system that you have in place.
The other tabs - probably the ones I would be interested in - are the onces that you have created by interrogating the tabs above and adding columns that you need.
Let me know what you think.
Peter
- I have excel for Mac version 16.37 - I will check! I marked as best response, but I will figure out how to mark as complete - thank you 🙂
The issue here is that VLOOKUP will return the first entry in your table. In this case you have several entries for Ontario Inc and it picks up the first entry which has null in the 2020 column.
The data as it stands does not lend itself to VOOKLUP.
There is a FILTER command (I think it is only available in Office 365 - what version of Excel do you have?). This would allow you to filter based on name and year (2020) then check the status.
I don't have access to Filter so can't demonstrate.
If you want to use VLOOKUP then you would have to create an 'artificial' column to lookup. For example, you can have a hidden column (before column A) which has the contract name and some unique identifier. You then use up VLOOKUP to find the correct entry. Unfortunaley, in the current data I can't see anything that you can use as a unique identifier. You know the data better than I do so you might be able to suggest something,
Peter
Thank you! peteryac60
Only just had a chance to look at this - thank you so much for resolving this step! This formula can be used in so many of my reports.
I still have other elements of the report to resolve, but just to finish the 'Active' contracts conversation, when I summarise the data (so that a client name only appears once) - how do I get the lookup value to return the right value?
For example in the attached main report on line 2 highlighted in yellow, I use a vlookup on the account name to find it's corresponding Status in 2020 and it returns a blank, when I know it should be 'Active'. this because the vlookup returns the first value which is blank - how do I get it to return the first 'populated' or 'not null' value? Or should I be using another formula?
Thank you!