Popup reminder based on date and additional celll values [Solved/Closed]

MikeTis 7 Posts Thursday July 4, 2013Registration date July 15, 2013 Last seen - Jul 4, 2013 at 11:09 AM - Latest reply: MikeTis 7 Posts Thursday July 4, 2013Registration date July 15, 2013 Last seen
- Jul 15, 2013 at 05:47 PM
Hi,
I am new to this site and have seen a lot of great information and helpful tips here.
I have been trying to figure this out for a very long time. Any help would be greatly appreciated.
I have a workbook that has dates sequentually on row 8, fields D8:GY8
I also have additional rows with name of an item and a reference for when to send it by manually placing an E1 or E2 etc in the correct field that corresponds to the correct item and correct date. For example in row 11, A11 has name of item and P11 has text E3. The E3 is in Column AD, Row 11 which corresponds to the date in row 8. This tells me that on July 11 (column AD, row 11), item number 3(text in AD11) which is HE-Public Sector (text in A11) needs to go out.
What I am trying to figure out is how to have excel set a pop-up reminder 1 day before the due date. When Excel looks at the dates in row 8, then looks down the column to see if there is text in any of the fields will popup a reminder saying E3, HE-Public Sector is due tomorrow. If no text move to nect field until reaches the end of the column and ends the routine. So there is a popup for where there is text and nothing for blank fields. This will run whenever excel is open and on a daily basis.
I hope this makes sense.
I do not see a way to attach example .xls

Thanks for any help you can provide and greatly appreciated.
See more 

10 replies

TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Jul 4, 2013 at 11:29 AM
0
Thank you
Hi Mike, good idea of attaching sample file, you will need to upload your file to a filesharing site like www.speedyshare.com or ge.tt and then post back the download link.

Best regards,
Trowa
MikeTis 7 Posts Thursday July 4, 2013Registration date July 15, 2013 Last seen - Jul 5, 2013 at 09:37 AM
0
Thank you
Hi Trowa,
thank you for your help.
I have uploaded the Excel to Speedyshare. The link to download is http://speedy.sh/ukjap/HE.xlsx

I have changed the file slightly for upload from my original posting so field references are no longer accurate.

Basically what I have been trying to figure out for a very long time now is how to have Excel look at todays date +1 in row 8, check corresponding column to see if there is text in the column, if there is pull the info from those cells and put into popup automatically one day before a column that contains text is due.
For example: on July 17, one day before E4 and E1 in column AK are due, excel would trigger a popup reminder. The popup would say something like NA - HE Public Sector (field A10) + E4 (field AK 10) and NA - HE Financial (field A11) + E1 (field AK 11) are due tomorrow.

There will be many rows of data so knowing when items are due one day before via a popup would be extremely useful versus searching down the worksheet manually.

Thank You for any advise or direction on how to accomplish this, it is greatly appreciated.

Mike
TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Jul 8, 2013 at 12:01 PM
0
Thank you
Hi Mike,

Your query is clear to me now.

Unfortunately I can't create a pop up with all results. (we are talking about lots of rows, right?)

Unless someone else provides a solution I can give you 2 options.

1. Create a pop up for every single entry. This means in your last example you would get 2 pop ups. This could get annoying when you have to click away i.e. 10 pop ups.

2. Create a hidden sheet which only appears when file is opened and when there is something due tomorrow. This sheet could contain a button to confirm the list (same as OK button on a pop up message) and hide the sheet again.

A temporary overview sheet has more customization / formatting options then a pop up message.

Let me know what you want. And please let your thoughts loose so we can find maybe a different solution.

Best regards,
Trowa
MikeTis 7 Posts Thursday July 4, 2013Registration date July 15, 2013 Last seen - Jul 8, 2013 at 05:42 PM
0
Thank you
Hi Trowa,
Thank You for looking at this.
I never thought about a hidden sheet.Option 2 seems like it will work perfectly.

2. Create a hidden sheet which only appears when file is opened and when there is something due tomorrow. This sheet could contain a button to confirm the list (same as OK button on a pop up message) and hide the sheet again.

Thanks again,
Mike
TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Jul 9, 2013 at 11:17 AM
0
Thank you
Hi Mike,

Good to see you liked my idea.

Here is your file:
http://www.ge.tt/7fBwRKl/v/0

Let me know if tweaks are needed.

Best regards,
Trowa
MikeTis 7 Posts Thursday July 4, 2013Registration date July 15, 2013 Last seen - Jul 11, 2013 at 04:04 PM
0
Thank you
Hi Trowa,
this works beautifully. I never considered or even thought of a solution such as this and it works beautifully. This will save a lot of time and missed deadlines.
Thank You
MikeTis 7 Posts Thursday July 4, 2013Registration date July 15, 2013 Last seen - Jul 14, 2013 at 06:54 AM
0
Thank you
Hi Trowa,
I have been trying to figure out how to add a second reminder to this, first reminder 3 days before and the second reminder 1 day before that you have provided, Thank You very much.
I was able to build the hidden page and modify the code you build for 3 days but I cannot get it to work in conjunction with the 1 day reminder. I tried adding the new code for 3 days as an additional sub but not sure how to code the function to work together with the code you provided.
I have uploaded the file I tried to modify, it is at http://speedy.sh/MavUA/MikeTis-HETEST2.xls

Any help would be greatly appreciated.
Thanks
MikeTis 7 Posts Thursday July 4, 2013Registration date July 15, 2013 Last seen - Jul 14, 2013 at 07:12 AM
0
Thank you
Sorry, the file is at http://speedy.sh/fEpKk/MikeTis-HETEST2.xls
TrowaD 2433 Posts Sunday September 12, 2010Registration dateContributorStatus September 17, 2018 Last seen - Jul 15, 2013 at 12:03 PM
0
Thank you
Hi Mike,

Good to see you tried first.

Here is your file:
http://www.ge.tt/1pnnWpl/v/0?c

Best regards,
Trowa
MikeTis 7 Posts Thursday July 4, 2013Registration date July 15, 2013 Last seen - Jul 15, 2013 at 05:47 PM
Hi Trowa,
This is perfect. I cannot thank you enough. I spent a lot of time on research, trial and error and you were able to come up with a solution that works brilliantly.
Thanks Again.