Mar 18 2019 01:16 AM
Hello There,
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
Thanks.
Mar 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.
Mar 18 2019 03:25 AM
Hi
I am using office 2013
Thanks for the response :)
Could you please help?
Mar 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.
Mar 18 2019 04:41 AM
@Celia_Alves
Please find the sample file.
Please follow original post to provide formula. (you shall find my original query explained in an image)
Many thanks.
Mar 19 2019 05:50 AM
SolutionHere you go! :) I developed a solution only based in formulas and not requiring Power Query.
Data Table:
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.
Results 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.
Celia Alves
Mar 19 2019 09:11 PM
Mar 20 2019 06:26 PM
Awesome! Glad that I saved something today! :D
Mar 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.
Mar 20 2019 06:51 PM
WOW.
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.
Mar 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! ;)
Mar 19 2019 05:50 AM
SolutionHere you go! :) I developed a solution only based in formulas and not requiring Power Query.
Data Table:
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.
Results 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.
Celia Alves