03-18-2019 01:16 AM
03-18-2019 01:16 AM
I need big help.
Attached images is self explanatory.
Let me know if you need any other information
Let's say, I need to create alert system for a specific date
03-18-2019 03:13 AM
Power Query would be one way to solve this. It comes with Excel in the most recent versions. Which version of Excel do you use? Can you please attach the file? I will help you with this.
03-18-2019 03:25 AM
I am using office 2013
Thanks for the response :)
Could you please help?
03-18-2019 04:16 AM
Please see here how to install Power Query in your Excel: https://www.myexcelonline.com/blog/install-power-query-with-excel-2013/
This is a powerful tool that you don't want to miss in Excel.
Let me know when you have it and please send a sample of the file. I will create an automation with PQ for you on your file. All you will have to do after that is to add new data and click a button to update your results.
03-18-2019 04:41 AM
Please find the sample file.
Please follow original post to provide formula. (you shall find my original query explained in an image)
03-19-2019 05:50 AMSolution
Here you go! :-) I developed a solution only based in formulas and not requiring Power Query.
I transformed the data table into an Excel Table. That will allow you to add more data at the bottom without having to update the ranges in the formulas. The formulas will always know that they need to look at the entire data table, regardless of the number of rows.
I added three extra columns to the table that will be responsible to check if the columns B, C and D have dates that coincide with the date in cell I2. You can hide these three helper columns if you'd like.
Cell I2 was given the name ChosenDate.
I added conditional formatting rules to the data table. This way, any date that matches the ChosenDate will be automatically highlighted. If you want this feature to keep working, you cannot paint the cells of the data table.
I added two helper columns to this table that are responsible to pick all the matching entries in the data table. You can hide these two columns if you'd like.
I added the formulas to the report columns.
The report table can show up to 25 results. You can copy the formulas down if you need more result rows.
The final file is attached.
I hope this helps.
03-19-2019 09:11 PM
Thanks A Lot :D
It works :D
You saved my **bleep** :D
03-20-2019 06:26 PM
Awesome! Glad that I saved something today! :-D
03-20-2019 06:41 PM
Here is the answer to your message asking how to adapt the formulas for when you want to get the results for dates in between two given date values.
Let me know if you have any questions.
03-20-2019 06:51 PM
Thanks again. You are Awesome, dear.
Even I also tried yesterday in separate way , it worked (Thanks to insights already shared by you before)
But you did it more smartly.
Advance Excel is beautiful.
03-20-2019 09:06 PM
Yes, Excel is the best!
The great thing is that there is no unique solution. I am glad that you were able to find an answer to the challenge on your own. Well done! ;-)
by Rajeev_Raghavan on June 02, 2020
by Aztrix on June 02, 2020
by cuong on May 27, 2020
by Ingeborg Hawighorst on May 13, 2020