Computer Support Forum

Automatic Email Reminder from Excel based on Date in Cell

Question: Automatic Email Reminder from Excel based on Date in Cell

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

http://forums.techguy.org/business-applications/710581-solved-automatic-email-alerts-using-2.html
Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!

Relevance 100%
Preferred Solution: Automatic Email Reminder from Excel based on Date in Cell

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Automatic Email Reminder from Excel based on Date in Cell

Please do not post duplicate threads.
One thread per issue.
Continue replies for this issue in this thread: http://forums.techguy.org/business-applications/856705-automatic-email-excel-based-date.html
Thank you.

Closing thread.

1 more replies
Relevance 103.82%

Hi All,

My name is Diego.

Can anyone send me code to automatically send me an email when the date listed in "column J" is the same date as today. Also, it needs to email only once and even if I am not running excel or at my computer. I want to use Microsoft Outlook and use the ClickYes program as well if this helps that was talked about by Zack Barresse in

http://forums.techguy.org/business-a...s-using-2.html
Essentially I have to be reminded of a reapplication for specific state licensures on healthcare courses I provide. I don't want to forget which courses I have to reapply for so I need to have a program that will look at a date which I have in column J and then email me to remind me of this.

BTW - I am using Outlook 2007 and Excel 2007 on Vista.

Thanks. I appreciate your help! Also, extra points and praise for the person who solves this problem!
 

Answer:Automatic Email from Excel based on Date in Cell

16 more replies
Relevance 92.51%

Hello all,

I'm receiving mail alert based on date only, it is not considering the month into the account.. Kindly suggest..

Attached the excel file with macro...
 

Answer:Automatic Outlook mail due date reminder based on Excel file

16 more replies
Relevance 92.51%

Hi Everyone!

This is my first post here. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a payment is 10 days away from the current date. The script should preferably run automatically every time the PC is running. without the excel file necessarily open.

In the attached excel file, An email should go of to -email address (Col. Q), with subject "Vendor Payment (Col. C) is due on Due date(Col. M)", and body "Dear Name(Col. P), please update the payment status".

Also, the script should put a check mark on Reminder sent column (Col. Q) after the mail is sent, the script should also check if the value of the cell is blank before sending email.

I have scored the forum for similar problems, and although I found a number of threads, I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,
 

Answer:Automatic Outlook mail due date reminder based on Excel file

15 more replies
Relevance 92.51%

Hi Everyone!

This is my first post here. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a project is 7 days away from the current date. The script should preferably run automatically every time the PC is running. without the excel file necessarily open.

In the attached excel file, An email should go of to -email address (Col. D), with subject "Project (Col. B) is due on Due date(Col. C)", and body "Dear Name(Col. A), please update your project status".

Also, the script should put a check mark on Reminder sent column (Col. E) after the mail is sent, the script should also check if the value of the cell is blank before sending email.

I have scoured the forum for similar problems, and although I found a number of of threads,I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,

Thanks
 

Answer:Automatic Outlook mail due date reminder based on Excel file

8 more replies
Relevance 84.1%

I have a personnel tracker that tracks required documents that need to be renewed every year. There are 4 of these and each has a seperate column for each date. They are J,L,T and U.

I would like excel to scan each of these columns every time the document is opened for any of those dates that are 60-65 days from expiring, I use Now()-300 to check this.

But if the cell is blank I want it ignored, which right now it does not. If it finds 1 or even 80 that are in that range I want 1 email generated to a list of management personnel.

Currently it generates how ever many are blank or expiring. I use outlook.

My data starts on row 3 and goes down to 331, the required and maximum number of people allowed on the contract.

If I can get just one email with a generic message I would be happy. But if I could I would like the info that is actually doing the triggering to be copied and pasted in the email to include the info in A, C and D.

Here is my current code, I would change .display to .send once I have it working. please help.

Private Sub Workbook_Open()
Dim Cell As Range
Dim DateRng As Range
Dim Msg As String
Dim olApp As Object
Dim olEmail As Object
Dim RngEnd As Range
Dim Wks As Worksheet

Set Wks = Worksheets("Work Site Info")

Set DateRng = Wks.Range("J3")
Set RngEnd = Wks.Range("J331")
Set DateRng = IIf(RngEnd.Row < DateRng.Row, DateRng, Wks.Range(DateRng, RngEnd))

'Change this to what you want.
Msg = "This is message is the bo... Read more

Answer:Solved: Automatic email from excel based on date

16 more replies
Relevance 84.1%

This is prasanna from india. i need a help in my company i want to trigger mail automatically based on the date.. its kind of probation tracking mail... Example :- IF an employee has completed three months automatically mail should trigger to concern person.. is it possible in excelThanks in advance

Answer:Sending automatic Email based on the date in row in excel

Look here:http://www.rondebruin.nl/sendmail.htmYou should be able to find something you can work with.MIKEhttp://www.skeptic.com/

2 more replies
Relevance 75.69%

Hi,

i have used the following to get automatic alerts setup when a due date is near:
https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/

but instead of this looking at the due date column, i would like it to look at a conditonal formatted column, which shows status as either "due" and "expired" depending on due date. i would like it send out to all those with "due tag and not "expired"

I have name in column A, details in column B, emails address in column C, status in column i, and reminder in column k
Somethng else i would like to do later on, if at all possible,is to get a particluar email from outlook be transfered to excel and and then emailed to specific people based on a status column. So if an email is recieved to say a project is extended or cancelled,, it contents is copied to excel and emailed back out to certain people.
Thank you for your support.
 

More replies
Relevance 75.4%

Good day,

I was looking for someone to help me in this:

Please see the attached excel file, which automates Groupwise mails for due date reminder. However, it generates individual mails for each due items, whereas, I want one single mail with all due dates. How do I achieve it?
 

More replies
Relevance 75.4%

Hi ! This refers to the subject of "Automatic email from excel based on date", and relevant thread http://forums.techguy.org/business-applications/927675-solved-automatic-email-excel-based.html

This was fantastic. Only one issue remaining.
This is generating multiple emails and even the items not due are generating blank mails.
I would like to generate only one mail with different items different expiration dates.
After that group the identical addressees and compose one message with all the relevant data.
Awaiting kind help.
 

Answer:Need Help reg "Automatic email from excel based on date "

7 more replies
Relevance 104.14%

hi there,

I have a list of memberships that need to be renewed at different dates (eg. every two or three years depending on type of membership)

Is there anyway i can make it to automatically send an email to remind me or another person the day it's due to renew?

The list is on excel with expiration date in one of the columns
thanks
 

Answer:Set email reminder based on excel

Hi, take a look at some of the posts, there are quiet a few that do just that, they may need a little editting for your purpose but...
Here's a simple module that does just that, all you need to do is wriet a amcro that fills in the blanks

Code:

Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String)
Dim eSubject As String, Sendto As String, CCto As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)

With Itm
.Subject = mSubject
.To = Sendto
If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
' .Attachments.Add (NewFileName1) ' Must be complete path
.Display ' This property is used when you want
' .send
End With
Set app = Nothing
Set Itm = Nothing
End Function


P.S. You forgot to mention the Excel versio you're using
 

1 more replies
Relevance 103.32%

I have a list of jobs (jobs schedule) to do... and they are required to be done in a by certain different dates regularly.

I have a specific jobs date for each task held in a colu. Is there anyway i can make it to automatically send an email to remind me or another person a day before it's due??

Another question - I have a basic dashboard when a job is overdue I want the cell to flash RED

thanks
 

Answer:Set email reminder based on conditions on Excel

10 more replies
Relevance 101.27%

Hello Everyone!

This is my first post here. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a project is nearing 3 hours, 2 hours & 1 hour away from the current date & time. The script should preferably run automatically every time the PC is running when the excel file is open.

In the attached excel file, An email should go of to -email address (Col. F), with subject "number (Col. B) is due on Due date and time (Col. E)", and body "Dear Name(Col. F), please finish your project asap".

Also, the script should put a check mark on Reminder sent column (Col. G) after the mail is sent, the script should also check if the value of the cell is blank before sending email.

I have scoured the forum for similar problems, and although I found a number of of threads,I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,
 

Answer:Automatic Reminder email from Excel

Hi, I suggest you do some searching, there are quiet a few posts with the same questions and the necessary solutions.
I worked on one or two but cannot remember the names, so just do a quick search and you'll find the answres with the necessary code.
Always to glad to help on if you're still stuck
 

1 more replies
Relevance 101.27%

If I am doing an automatic email reminder when something is past due:

do I always have to open the Excel document for the macro to run and the reminder to sent out?

Thanks,
 

More replies
Relevance 101.27%

Hi,
I'm kinda new to VBA and excel programming and am looking for a way of sending an automated reminder 60 days before the expiry of training already completed. column G contains the email recipients, Column E has a title Asbestos Awareness and the cells in column E the expiry dates for employees.
I would appreciate if anyone has sample code I could try.


Thanks sooooooooooo much for any help or advice.
Regards
Vinny
 

Answer:Automatic Email reminder in excel

Hi Vinny,
welcome to the forum, you're the second one this week with the same question, and just like the other poster I do not think you took the time to search for similar posts.
I suggest you do hat, there are several posts with the similar questions and also with the answres and code provided.
 

1 more replies
Relevance 100.86%

Dear All,

I am creating a tracker file for Purchase Orders in which end date in column 'J' is very important.
Column 'J' has following conditional formatting
1. If cell date is in between todays date + 14 days --- YELLOW
2. cell date <= today's date ------------------------- RED
3. cell valus > today + 14 days ---------------------- GREEN

So now when the cell turn Yellow I want the excel should send the mail to the concerned person whose mail id will be mentioned in same row of column 'G'

One more requirement there should be two mails. Reminder 1 & Reminder 2.

I use officer 2010 & mail is outlook 2010 & OS is windows 7.
This file will be on share point. This file may not be opened everyday.

Please reply with the procedure as I am not a programming/ IT person... I may not understand all terms.. please try to simplify the response.
Thanks in advance for all the help.
 

Answer:Auto mail from Excel to Outlook based on date in cell

Hi there, welcome to the board!

You'd want a location to mark when an email was sent. I'm assuming you want a worksheet change event for this, which will basically always run when a cell on this specific worksheet is changed. There are other events you could use to fire it off, like the calculate event. You could, if you wanted to, assign this functionality to a button, but then it wouldn't be automatic.

The below code does what you ask. It goes in the worksheet module of the worksheet your data is on. To get to it, right click the sheet tab name and select 'View Code', then paste this there.

EDIT: The locations to mark when an email was sent (btw) were columns K and L, as you'll see them set as constants at the top of the code. You can change those letters to any column you want to house it in. It just puts the current system date in those cells, and that will be checked when the values in column J are checked. If nothing is there it assumes an email hasn't been generated yet and will do so. But if it has a value - any value, it will ignore that row.

Also, I assumed a 'yellow' highlighted value was your first reminder, and a 'red' highlighted value was your second reminder. It uses this as text in the subject and body of the email.
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Const ReminderOne As String = "K" 'column letter
Const ReminderTwo As S... Read more

1 more replies
Relevance 99.63%

Hi Everyone!! This is my first post here. I need your help in developing a method of sending automated emails on Gmail when the due date of Insurance payment is 7 days before the current date and an email when the due date has expired.

So as per the attached excel file an email should go to (Col H) when due date (Col F) is 7 days before current date, with the message "Payment of Rs. (Col E) is due for Policy Number (Col D), (Policy name (Col C)) for (Owner name (Col B))"

I have searched through various forums for similar problems but could not really find a solution, as I am not proficient with VBA to tweak the code as per my needs.

Any help would be really appreciated!

Thanks
 

More replies
Relevance 99.22%

Hello Everyone!

This is my first post here. I am using Excel 2013 OS win 7. I need your help in developing a mechanism to send an automated outlook mail, when the due date of a project is nearing 3 hours, 2 hours & 1 hour away from the current date & time. The script should preferably run automatically every time the PC is running when the excel file is open.

In the attached excel file, An email should go of to -email address (Col. F), with subject "number (Col. B) is due on Due date and time (Col. E)", and body "Dear Name(Col. F), please finish your project asap".

Also, the script should put a check mark on Reminder sent column (Col. G) after the mail is sent, the script should also check if the value of the cell is blank before sending email.

I have scoured the forum for similar problems, and although I found a number of of threads,I am not proficient enough in VBA to modify them to my needs.

I'd really appreciate any help,
 

Answer:Automatic Reminder email from Excel 2013 in Windows 7 pc

closing duplicated post
continue here
http://forums.techguy.org/business-applications/1141421-automatic-reminder-email-excel.html
 

1 more replies
Relevance 99.22%

hi !
I have a spread sheet of 100 of employees , i like every time the expiry date come for there id a notification email come to me , i attach the example excel sheet please help me with that, i am just learning VBA not very good in it i am using windows 8
 

More replies
Relevance 98.81%

HI, Its my first post here. I need to send reminder mails from outlook based on excel reminder dates.
For this i should not open excel neither i know VBA scritping for this.
Please provide help on this.
As i attached my file i need list of assets need to be calibrated ahead of 45 days of calibration due date.
 

More replies
Relevance 96.76%

Hi,

New here. I dug up a thread that Zack Barresse solved many years ago. I am looking to do the exact same thing. The link to the thread is below. My file is infinitely more complicated than what that user was asking for so I need a bit more help tuning the VBA. Link: http://forums.techguy.org/business-applications/710581-automatic-email-alerts-using-excel.html

Some specifics:

- I am using Outlook not Express
- Excel 2007
- All the functionality is complete for monitoring several live streams of securities data with several trade indicators.
- It is consolidated onto one sheet for manual monitoring (Picture below). Basically takes copious amounts of data and reduces it to just IF and AND functionality for the triggers for easy use from all the other sheets.
- The workbook will be open and running/refreshing on its own 24/7 as it is now.

I am a busy guy, I just need the VBA to automatically email me remotely when any of the 7 currency pairs causes a trigger when I am on the go. I can log trades from an app on my phone.

One other hurdle would be that if say (Using percentages to keep it simple) that a trigger would be if something reached as high as 80% to send the notification email. But where the system refreshes every 60 seconds it shouldn't send another notification each time it remains at or above 80%. Just the once. It may remain there for hours and that is a lot of emails.


Thoughts? and many many thanks in advance.
 

Answer:Excel - Auto Email based on cell value

10 more replies
Relevance 95.53%

Hy guys

2nd time i am posting stuff for help, and as i was helped before i will again look forward the response.

I have a file of excel, in which i am sending emails to different candidates of admission, with scan letter placed in the same folder by name.

I want to edit this code, which could select attachment based on Column A list adjacent to the email address

I am attaching the file also pasting the code

Sub Test1()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

Set OutMail = OutApp.CreateItem(0)

strbody = "We at Graduate School of Engineering Sciences and Information Technology are extremely pleased to know that you have selected Hamdard University as preferred choice for your graduate/post-graduate Studies. " & vbNewLine & vbNewLine & _
"Hamdard University is a pioneer Higher Education Institute (HEI) of Karachi producing Masters and PhDs in the fields of Engineering, Computer Sciences, Information Technology, Energy and Environment since 19... Read more

Answer:Attachment based on cell value in a excel email macro

anybody ???
 

2 more replies
Relevance 94.71%

I am working with the attached spreadsheet in Excel 2010 and am trying to figure out how to code certain parameters that will make Excel send myself, my client or other individual an email (with text in body) if certain dates have not been entered into particular cells, or if a cell has exceeded a certain number of days in a particular cell. I have attached a sample spreadsheet and have listed at the bottom 8 points in which I need an email sent, what the trigger is and what the action (email sent to) is.

I just know enough to be very dangerous with Excel but have found that there is a way to code in Excel to send emails which would greatly help my business but I just don't know that much about codes at all.

Can anyone please help me??

Thanks!!
 

Answer:Excel Coding to Send Email based on Cell Entry

Hi, welcome to the forum.

I suggest you do a find in the forum, there are many posts that gao about this and there are many answers, I'm sure there is one that will help yu and of course one of us can help you if you're still stuck
 

2 more replies
Relevance 94.3%

Hi Guys,Currently am working on a Excel VBA to alert me through Outlook email based on the following conditions,I've the deadline dates for each tasks listed in Column 'G'. Now i want the VBA to check this deadline date and if the deadline date is 7 days from today, then it should copy the particular Row data and email the data to my email address.This should be performed everyday automatically, without opening the excel sheet.Am very new to VBA. So,Your help is much appreciated.Cheers,PK

Answer:Excel VBA To Email Row Data Based on Date

re: "This should be performed everyday automatically, without opening the excel sheet."I don't think that Excel or VBA for Excel is not going to be able to do this with opening the file - or at least some file.You can't have a macro running inside a closed file, constantly monitoring itself. Something has to trigger the code and/or update the formula that checks the dates, such as the file being opened or perhaps another open file pulling data from the closed file, etc.I guess you could schedule a windows task to open the file once a day or something similar, but there is going to have to be some "action" that initiates the running of the code.

14 more replies
Relevance 93.48%

I need a code that will allow the workbook to be emailed when Column A is populated by certian numbers. The numbers in column A corespond to particular email addreses. This is the code I've been working but it isn't functional.

Sub Email_Out()
If Worksheets("Sheet1").Range("A5:A200") = "190030001" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
ElseIf Worksheets("Sheet1").Range("A5:A200") = "190450025" Then
ActiveWorkbook.SendMail Recipients:=("[email protected]")
End If
End Sub

All help is greatly appreciated!
Mikey
 

Answer:Solved: VBA email excel workbook based on cell values using; If Then ElseIf Please he

16 more replies
Relevance 93.48%

Hi This is a follow up to

http://forums.techguy.org/business-...emailing-multiple-recipients-excel-based.html

I would like to be able to do the same

My excel sheet keeps a list of Email addresses on column B (with duplicate email addresses), and their particulars from column C (Item price, purchase date, etc) onwards.

I need the vba to email multiple recipients (those with the "notification" column E field marked as yes) with their purchasing details in it. I need to collate each row with the same email address & marked Yes so that only one email is sent.

eg: email will have in the body

Your order are ready to collect:

row 2 information
row 5 information
row 9 information
It should also prevent multiple emails to the same email address. I would like not to have to change the Notification column to acheve this.

Thank you for your help.
 

Answer:Emailing multiple recipients from Excel Based off Cell Value Collate to one email

6 more replies
Relevance 93.48%

Good Afternoon - this is a follow-up to an earlier post that has been closed.

http://forums.techguy.org/business-applications/1090938-emailing-multiple-recipients-excel-based.html

I would like to do something similar.

My Excel sheet has a list of Email addresses in Column A (with duplicate email addresses).
I have several other columns with data that that I would like to have appear in the body of the email in Outlook.

I need to collate each row with the same email address so ONLY 1 email is sent to each recipient.

Is this something easy to do?
I have little to no VBA coding skills

Attached is an Excel mockup of what I am attempting to accomplish.

The 1st tab called "Sample Data" is basically the raw data I want to leverage.
(which I also tried to display below)
Email Address .....Invoice Number .....Date..... .....Dollars
​ [email protected] .............1 ...............7/3/2013 ......$10,000
​ [email protected] ..............2 ...............7/9/2013...... $50,000

[email protected] ..........3 ...............7/9/2013 ......$40,000

[email protected] ............4 ...............7/10/2013 .....$1,000

[email protected] ............5 ...............7/11/2013 .....$3,000
​The 2nd tab called "Body of Email" is an example around how I would like to see the data appear in the email.
Even though [email protected] appears 3 times in the above example, I ONLY want him to receive 1 email that contains 3... Read more

Answer:Emailing multiple recipients from Excel Based off Cell Value Collate to one email

8 more replies
Relevance 93.48%

I've read the previous post with the same issue, but I'm unable to understand how to use the other codes posted within my product. I would like to send an email based on a date. I will attach my document so it is easier for me to explain the requirement. Columns L37-L45 have due dates - I would like the email to be sent 60 days prior. I have posted some mock emails in R37-R45 and the email message in the EMAIL workbook tab. Any assistance would be greatly appreciated.

Thank you so much!
 

Answer:Auto send an email based on date in Excel

Welcome to the board.
I've had to save it as 2003 version but the code works under 2007

See attached my copy of your sheet with the code in ThisWorksheet module.

This just a simple way of doing it and you will have to edit it for your needs but maybe it can put you on the right track.
 

2 more replies
Relevance 92.66%

I have read reviews on forum on same . But still could not find a soultion probably becoz i am not savy with excel . We are basically in to procurement of material . Currently the problem we are facing is that we are not able to track ,whethe the credit period of the supplier has finished and we have paid him or not ? From best of my excel knowledge i was able to establish a formula for same and was sucessful too .i was getting information on Gap b/w payment date and todays date .Moreover I got visual indicator for same , by conditional formatting . Now my boss wants me to make a provision in the excel sheet that once teh payment date has expired , he should keep on getting reminder for same as outlook message with suppliers name and order detail . I have tried alot for same on base of information given on the forum and infact downloaded and installed Click yes active. ver1.2 too Since i dont know VB so i am not able to solve thsi problem. Can any one help me on ths issue as it is important for my promotion .The file is ready for me and can be uploaded on request .
 

Answer:Automatic Email Alerts for conditions in excel cell

9 more replies
Relevance 92.66%

Hi, I am really new to excel so I am hoping to get some help here. I have searched through the forum and tried several codes, but I was not able to run it successfully.

Basically in my excel file, i have a long list of documents with their respective expiry dates. I want excel to send me an email when it is 6 months before the expiry date.

I have uploaded a sample file on what my worksheet will look like.
I am using Microsoft Excel 2010.

Thanks!
 

More replies
Relevance 91.84%

Windows 7 Excel 2010
I have a spread sheet that has entries containing email addresses. When I select one of these cells, Outlook automatically opens a new email with the email address in the TO field. How do I prevent this from happening? Earlier research suggested an option
on the Tools menu, but, of course, there is no Tools menu.
P.S. I selected the Archive Forum Category only because there isn't a forum in the list for Office, which seems odd.

More replies
Relevance 91.43%

Hi everyone

I hope all are fine and reading my message in the best of their health.. I am stuck with a job to do on various dates where i have to email those candidates whome progress reports are due, which due dates are mentioned in the sheet.

Can some body please help me to come up with a excel automatic mail solution that an email should be generated via outlook without opening the file or even if needed opening the file. The email should contain

Dear Student

Your progress report / proposal defense is due on (due date),

regards,
this email should be generated and sent before one month and one copy on the date on which it is due.. the cc list also needed to be grabbed from somewhere, where cc of this email should go.

I shall be very thankful if somebody can look the attached file and help
 

Answer:Solved: Excel Automatic Email on due date

16 more replies
Relevance 91.43%

Hello,

This is my first post here. I have been searching all over the internet trying to find an answer to my excel problem. I am pretty new to excel and my goal is to have excel automatically send an email out to me when the due date of a training is approaching. I have already set it up using conditional formatting to fill the due date box yellow if a due date is less than 30 days away but I would like excel to email me a reminder so I don't have to remember to check the spreadsheet every month.

I have it set up with a training summary sheet that shows the # of trainings due within 30 days so what I really need is a macro to send if the cell is greater than 0.

I have done a lot of searching but am new to excel programming and can not figure out how to adapt the codes I have seen to satisfy my needs. Or where to enter them

I attached the workbook I am trying to do this for. Im looking to apply the macro to the 'Training Summary' sheet.

Any help would be greatly appreciated.

Thanks,
Brandon
 

Answer:Automatic Email From Excel When Due Date Is Approaching

Hi,

In order to do this we need more information.
Will the workbook be opened every day?
If so you can set the code to check the date and E-Mail you.
If the workbook will not be opened every day then you can use the "Task" manager
and have it open the workbook to check the date.
You can also set a code than when the workbook is opened it will check the
Date and if you need to review it will activate the sheet and then Highlight the Date.
 

3 more replies
Relevance 91.43%

Hey all
I am having a problem trying to send automated messages from the below excel file using the date column
all i need is the sheet to send an email alert a day before the dates mentioned in all the sheets in the workbook

Thanks in advance
 

Answer:Automatic Email using the date on the excel sheet

Hi,

See if this site helps.

http://www.rondebruin.nl/win/section1.htm
 

1 more replies
Relevance 90.61%

Hi All!

I am having major difficulty figuring out excel. I am using a spreadsheet and want excel to automatically send an email to the user in that row when a contract is expiring. Within the row I have the specified user's email, the end date of the contract, and when the reminder email should go out. I have tried playing around with Macros and VBA coding, but I have no idea what I am doing. I am using excel 2003. Any help would be greatly appreciated!! I am using Outlook as my email. Have questions please let me know!

-J
 

Answer:Email automatically sending to user when cell is at a certain Date Excel 2003

16 more replies
Relevance 86.92%

Hi All,

I need an help one excel formula ,i have specific range with prices, but when ever there is a change in value in the range then one particular need to be updated with now() formula. Find below the snap shot of desired data....

Product Changes happened Jan'13 Feb'13 Mar'13 Apr'13 May'13 Jun'13 Jul'13 Sep'13
Apple
Orange
Pineapple
Fruits
In the above data i have values for all product in all months. if there is any change happened then in the changes happened column need to be updated with now() formula.

Thanks in advance...

Vinwin
 

Answer:Automatic date update in a cell when another cell's value changes (as calculated

9 more replies
Relevance 85.69%

Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you
 

Answer:Excel - Copy paste cell into range based on another cell

12 more replies
Relevance 85.69%

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

More replies
Relevance 85.69%

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

More replies
Relevance 83.23%

I have an excel spreadsheet that contains a series of 5 number and a date. I am looking for every occurrence of a second number in a row if I find the first number anywhere along that row.for example - I want to find every occurrence of 27 if I find 6 first.9/26/12 2 18 19 35 369/25/12 12 20 35 36 379/24/12 11 18 22 32 399/23/12 6 10 27 35 399/22/12 6 23 27 32 379/21/12 2 33 34 35 399/20/12 13 17 18 19 379/19/12 10 12 15 27 349/18/12 4 16 17 35 389/17/12 2 3 5 9 219/16/12 6 15 22 33 36

Answer:Looking for cell contents based on another cell in excel

First, a posting tip...Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum. As for your question, what do you mean by "find" every occurrence of e.g. 27? Do you want the address returned? Do you want to highlight it?Please clarify.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

10 more replies
Relevance 83.23%

In VBA, I'm having trouble with the following: I would like to move to a cell that is "x" number of cells to the right of the current cell selected. And "x" equals a value in cell 1E After that cell is selected, I would like for the column that this newly found cell is in and the 200 columns to the right of it, to be deleted.Thank you!

Answer:Excel.VBA move to cell based on other cell

re: "In VBA, I'm having trouble with the following..."When you say you are having trouble, does that mean you have tried some VBA code that is not working?If so, why not post what you have and we'll see if we can point of the "troublesome" areas.

4 more replies
Relevance 82.82%

I have a sheet with several hundred rows. Starting in Row 4 through the end of the sheet are mixed dates in column 1 and ALL rows from Row 4 through the end of the sheet hidden. I want to be able to type in a date in Cell A1 and have all rows that have that date in Column 1, Row 4 through the end of the sheet to become unhidden. Secondly, I would like to type some other code like "999" or something in Cell A1 to have ALL rows become unhidden. Thirdly, I would like to either type another code or just delete anything in Cell A1 and have ALL rows from Row 4 through the end of the sheet to become hidden again.
Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Duo CPU T6600 @ 2.20GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 3963 Mb
Graphics Card: Mobile Intel(R) 4 Series Express Chipset Family, 1853 Mb
Hard Drives: C: 453 GB (298 GB Free); F: 931 GB (710 GB Free);
Motherboard: TOSHIBA, Portable PC
Antivirus: Webroot SecureAnywhere, Enabled and Updated
 

More replies
Relevance 82.82%

Hi,I have a new query.I want to be able to send an email alert from excel to an email address based on date criteria:Basically, if Cell , say, K14 (a date field) is older than todays date ( defined by a cell , say Q14 (=now() ) and cell S14 is =0 then the email gets sent and a flag in cell S14 is set to 1.It may be that I modify the code later to send a second reminder when the condition is met as date is date+7 and S14 code is 1, followed by changing the flag in S14 to 2.I have seen some codes on the internet that will just send the excel sheet.Also they use Outlook, whereas the email application we use in the office is Lotus Notes. Can anyone help with the code and the server side of emailing?Thanks and Merry Christmas to all. Talt.

Answer:emailing from Excell based on cell date

Hi,Here is a suggestion for an automated e-mail from Excel.To automate this, the code is placed in the Worksheet_Change event of the worksheet with dates in cells K14 etc.The aim is that when there is a change on the worksheet, this code is automatically run (its part of Excel's event handling). The code tests if the changed cell is the one containing the latest update date. So this code only runs if the update date is changed.The code tests that the last e-mail date was earlier than today to avoid repeat e-mails. The date the new e-mail is sent is added after the e-mail has been sent.Here is the codePrivate Sub Worksheet_Change(ByVal Target As Range)
'only run this code if K14 has changed (the update date)
Set rngUpdate = Application.Intersect(Target, Range("K14"))
If Not rngUpdate Is Nothing Then
'But only send if last e-mail date is earlier than today
If Range("S14").Value < Range("Q14").Value Then
'create the e-mail object
Set objEmail = CreateObject("CDO.Message")
'set e-mail SMTP configuration
With objEmail
With .Configuration.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
= "XXXXX"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") _
= "30"
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") _
= "2"
.Item("http://schemas.mic... Read more

8 more replies
Relevance 81.59%

I'm working on a spreadsheet that lists monthly recurring expenses. I have one cell on the sheet listing the current date (using the "=Now()" formula), but I'd like to have the other cells change at certain times throughout the month.

For example, if one cell currently says "April-15", when the actual date reads "April-16", the respective cell would change to "June-15". This way, whenever the spreadsheet is viewed the next recurring payment date will be seen.

I know this is the wrong tool for something like this - I'm using SharePoint at the office with a calendar list with workflows that shoot off emails in a much better put together format - but this is just for home use and I'd like to keep it all in one document if possible.

Any ideas or suggestions would be greatly appreciated. Thanks in advance.
 

Answer:Excel 2010: Cell Date Change with Current Date

The way I read it was that you have fixed recurring payment dates, so you just want to know the next date based on the day today. Hope the attached might assist.
You need to list the payment dates somewhere and then the formula looks up the next date. You might want to look up today plus 1 (or something) to reflect the fact that if the next payment date is today, there's a good chance you'll not get any unplanned payments on that run.
 

1 more replies
Relevance 80.77%

I know this is an advanced question. So I will be specific.A61 =TODAY()Now, based on date, I want to add 2.5 to the numerical value in cell B22. So, when the first of the month hits, I want the value in B22 to have 2.5 more added to it.I know it would be something like, WHEN A61 VALUE=1stOFTHEMONTH;THEN B22VALUE+2.5()I know what I want it to do, however not how to apply it. If anyone can help me out, please, and thanks for even entertaining me. Just to give more information, this is a military tracker for leaves and passes. I want it automated.

Answer:Dating formula auto cell update based on the date

Possibly a macro would even be helpful. Say, for instance, the value in B22 is 30. We all now that the next value would be 32.5. I just wan to know how to do it so I'm not number plugging for 45-60 people every month.

4 more replies
Relevance 80.77%

Hi....
Anyone can help me to advice how do i write VBA for auto email reminder for due dates.
I have tried one from internet guidance but it doesn't work.

i want the email reminder to a list of IDs before 60days and it's much helpful if the program can send the email without opening the excel sheet.

please help.
i have attaced the file.

thank you so much.
 

More replies
Relevance 79.54%

how to set auto reminder when a due date is reached mentioned in excel EVEN WHEN EXCEL IS SAVED AND CLOSED?
 

More replies
Relevance 79.54%

Hello.
I have been asked to send warning e-mails to a short list of adresses from an Excel file where reminder dates correspond to the actual date, and I have no clue how to do this with Macros. Iv tryed to understand how some examples of macros dealing with Excel and e-mails with no success as I still have a lot to learn

Here is my problem : I have a spreadshhet containing company names (col.A) and contract renewal dates (col.D). there are about 80 companies listed. When the actual date corresponds to the reminder date an e-mail containing the company name, a fixed blabla and the reminder date should be send to 3 known e-mail adresses.
Could this be done with an Excel Macro
Im using Windows 7 32bits, Office 2010 with Outlook as mailing system.

Many thanks in advance.
Robert
 

Answer:Solved: Reminder date in Excel sends an e-mail

16 more replies
Relevance 79.13%

Hello,

I was able to successfully apply Hans' module and have the spreadsheet send an email when it was opened if something was out of the calibration date.

My question is now if it is possible to have it send 2 weeks in advance, and then again in 1 week later. I know that because the cell will be occupied that it was initially sent, it will not read it again for another send. Is there a way to automatically clear a column so that it just sends an email every time its opened within that range?

Finally, where do I add the column for a CC email?
Code:
Option Explicit

Public Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim EBody As String
Dim ws As Worksheet
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Set ws = Sheets("Master Equipment LIST")
Sheets(1).Select
lRow = WorksheetFunction.Max(3, ws.Cells(Rows.Count, "K").End(xlUp).Row)
If ws.Cells(lRow, "K").Value = "" Then Exit Sub
For i = 2 To lRow
toDate = Replace(Cells(i, "K"), ".", "/")
Debug.Print WorksheetFunction.Days360(Date, ws.Cells(i, "K").Value)
If WorksheetFunction.Days360(Date, ws.Cells(i, "K").Value) <= 14 And Len(Trim(ws.Cells(i, "M").Value)) = 0 Then
' If Left(Cells(i, 5), 4) <> "Mail" And toDate - Date <= 30 Then
t... Read more

More replies
Relevance 78.72%

Hi,

I am in dire need of a solution wherein I want outlook to shoot an automatic email based on date from calendar or excel. Basically I have some users assigned some privileges which are assigned for a specific period. I need the mail to go out to them may be a day or two prior to the target date.

I came across this thread:
http://forums.techguy.org/business-applications/1129238-automatic-outlook-mail-due-date.html

I copied the code the in outlook VBA editor (i already have one script there and since being new to this i do not know if two scripts would be ok to have in there.... ...ScreenShot attached). However just to try when i ran the rule it gave me an error (screenshot attached).

I can't reply to the same thread since that is closed.

can you please help in this regards...

-regards,
gsg
 

Answer:Solved: automatic mail delivery based on date/time

9 more replies
Relevance 77.08%

Hi all,

I have a workbook containing 30 worksheets. The first sheet is named Master, while the other 29 worksheets are named with cities where we held branch offices (Los Angeles, New York, Chicago, Washington D.C., etc.)

I am trying to write a macro that will copy the contents of a row in the Master sheet to a particular worksheet based on the contents of the cell in that row that contain the location.

To illustrate:
Master sheet:
Columns are: Staff Name, Location, Project status

We have over 5000 staff.
I am trying to write a macro that will copy each staff's record to the worksheet corresponding to his/her location, depending on their project status.

So I am planning to use macro buttons labelled A, B or C (these are the values in the Project status column). So when I click on the button labelled A, the macro will go through all 5000 employees, and for those employees whole project status is "A", will copy that employee's record into a worksheet depending on his/her location.

For example:
Staff name, Location, Project Status
Joe Doe, Los Angeles, A
Jane Davis, New York, A
Mike Smith, Los Angeles, C

When I click on the macro for "A", it should copy Joe Doe's record into the worksheet labelled Los Angeles and copy Jane Davis' record into the worksheet labelled New York.

Thank you so much for your help. I've been searching all over the place for the command to use to reference the worksheet.
 

Answer:Excel - how to reference a worksheet based on value of a cell

For the love of PacMan, make sure you save your file BEFORE you try this. Try it on a copy of the file, even.

HTH,
Andy

Sub A_Status()
'select the list of staff names on Master sheet, then run
For Each Cell In Selection
If Cell.Offset(0, 2).Value = "A" Then
DestinationSheet = Cell.Offset(0, 1)
Cell.Resize(1, 3).Copy
Sheets(DestinationSheet).Select
'assumes each location sheet has a header row
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Master").Select
End If
Next Cell
Application.CutCopyMode = xlCopy
End Sub
 

2 more replies
Relevance 77.08%

I have several large spreadsheets containing information about building contractors and numbers of items fitted into houses.I would like to colour code many of these items so that they are specifically excluded (or included) in the sum totals at the ends of the rows and columns.Is it possible to sum rows and columns based on the colour of the text or cells.If so please explain in terms as simple as possible as I am not yet into manual coding etc. within Excel.

Answer:Sum in Excel based on text (or cell) colour

You can use conditional formatting rules to set the colour of a cell from the Format menu. I'm still trying to work out the next bit but I thinks it's an array formula but I don't know much about them.

9 more replies
Relevance 77.08%

I would to insert a row in excel only if by comparing two valves in seperate sheet and if the value matches copy the entire row paste it by inserting a new row below it. can this be done easily with formula or should we have to go macro. please help me with this

Answer:How to insert a row in excel based on values in the cell

A formula can not insert rows, so you will need a macro.We will need more details before any help can be offered. What values are be compared, which sheets, etc.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 77.08%

Sounds like a simple thing but Im having troubles finding an easy answer. Still searching .

I want to create a catalogue for a customer of mine (basic but might be more extensive later)

showing a thumbnail picture and then a short product description. Then he can go into the doc and correct the descriptions etc.

Not sure if excel or word or other is the best place.

He has over 200 product pictures stored on disks and folders on hard drive. I have corresponding description in excel based on a code that is the photo name (+.jpg).

So, I have the product files name as HB1000.jpg and all I want to do is show the picture (say at size 100x100)

Ideally Id like to build a reference based on the code and not have to insert each file.

Thoughts??

So far tried creating a formula/reference in excel with Code HB1000 (Cell A2) and image cell contains =c:\productimage\$A2$.jpg - can not get the pic to display
 

Answer:How to display images in Excel based on cell ref

The answer is already on here, I posted it fairly recently, it requires VBA to do it well.
see this thread
http://forums.techguy.org/business-applications/723855-solved-insert-image-based-cell.html
 

2 more replies
Relevance 77.08%

Hey all:

I'm have really no experience with coding, so I'm out of my depth trying to do this myself. Hopefully someone can help, so here goes!

Essentially, my excel sheet keeps track of dates of when files need to be renewed. They need to be renewed every 60 days. So I have an "IF" formula that tells me if it's time for renewal.

What do I need to do to get an email notification whenever a file comes up for renewal? I've attached a sample sheet.

Best,
Nick

PS.
Using Excel/Outlook 2010
ClickYes Pro is installed (don't know if it will be necessary or not)
 

Answer:Solved: Emailing from Excel Based off Cell Value

16 more replies
Relevance 76.26%

Hello,I have a workbook in excel with a number of different worksheets. I would like a way to set it so that when a user prints the workbook it only prints specific pages based on whether a specific cell in each worksheet has content or not. Here are the names of each of the worksheets along with the cell that is of importance:Main - I never want this to printNew AX43Cont_Sheet B3Orange AX43O2 AX43Vodafone AX43T-Mobile AX43Three AX43So, for example, if the cell AX43 on the worksheet named "Orange" has something in it, then when the user presses print, I want worksheet "Orange" to print. Same for all the other worksheets where the specified cell above is full. Any worksheets where the specific cell is empty, then I want that worksheet omitted when printing the workbook.Is this possible?Many thanks in advanceAmy.

Answer:Excel:Print specific pg based on cell content

Try this:Press Alt-F11 to open the VBA editor.Double Click the ThisWorkbook module in the left hand column.Paste this code in the pane that opens. It will run automatically each time the user prints the workbookRead the comments to see what it does.Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Disable events while code runs
Application.EnableEvents = False
'Loop through sheets
For shts = 1 To Sheets.Count
'Read Sheet Name
Select Case Sheets(shts).Name
'If Name = Main, Don't Print it
Case "Main"
Cancel = True
'If Name = Cont_Sheet, Check B3, print if not empty
Case "Cont_Sheet"
If Sheets(shts).Range("B3") <> "" Then _
Sheets(shts).PrintOut
'All other sheets, check AX43, print if not empty
Case Else
If Sheets(shts).Range("AX43") <> "" Then _
Sheets(shts).PrintOut
End Select
Next
Application.EnableEvents = True
End Sub

6 more replies
Relevance 76.26%

Any Excel gurus who can help on this?Working on a spreadsheet with lots of names in, and need to get cells to change colour/font characteristics depending on contents (which name is picked from drop down list)Conditional formatting would be OK, except there are too many names....Is there any way to do this using an "IF" formula for example?All help appreciated!

Answer:Excel text colour based on cell contents

I think that you would need a macro like the following to do this:Sub ColorThem() Dim Ndx As Long For Ndx = 1 To ActiveSheet.UsedRange.Rows.Count If LCase(Cells(Ndx, 1).Value) = "account" Then Rows(Ndx).Interior.ColorIndex = 3 End If Next NdxEnd Sub

4 more replies
Relevance 76.26%

I have some excel knowledge but am not too proficient in VBA. I am looking to generate emails based on the any of the conditions I have specified below for column G "Current Approved to Date"

Conditions:
1. =AND(($G9<Today()),($I9=""))
2. =AND(((J9-G9>3)),($I9="Applied for Ext."))
3. =AND((J9>G9),($I9="Active"))

If any of these conditions are met I would like an email to be generated.
(SIMILAR VBA) https://forums.techguy.org/threads/solved-automatic-email-alerts-using-excel.710581/page-2
 

Answer:Excel Generate emails based on conditions of cell.

10 more replies
Relevance 76.26%

Hi All,

I need to make a macro that will add borders to certain cells.
I have a generated report from another product so I don't know how many rows there will be until the report is generated. The number of rows is specified inside the report, so I do have access to it. It is stored in cell C5.

So what I want to do is add borders around C5 number of rows starting at A7..E7

I tried using a rule, to just put borders around cells that contain data, but that won't work because sometimes cells are blank and I still want the border around them.

Help?! Please

Thanks a bunch!!!
Lori
 

Answer:Excel Macro to add borders based on cell contents?

Hi Lori

Here is a macro that will take the value in C5 e.g. 10 and then apply borders to all cells in A7 to E16
Code:
Sub add_borders()
lRow = Range("C5") + 6
Range("A7:E" & lRow).Select
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlC9ontinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
 

1 more replies
Relevance 76.26%

Need some help sorting a large data output to different sheets based on Column A value, seems like this should be easier than what I am doing but I can't get it to workData coming in example
A C H I
4 Dept Product Code Sch # Prod #
5 DeptA
6 DeptA 101 12 12
7 DeptA 102 34 33
8 DeptB
9 DeptB 201 10 11
10 DeptB 103 23 25
11 DeptB 301 45 40
I want to copy this data to Existing Sheet Named "DeptA"
A C E
3 Prod Code Sch# Prod#
4 101 12 12
5 102 34 33
and then same thing for "DeptB" sheet
A C E
3 Prod Code Sch# Prod#
4 201 10 11
5 103 23 25
6 301 45 40

They can be kept in current order, if Product Code is blank skip to next line, if Sch # is blank skip to the next line, copy Prod# whether it is empty or notThanks

Answer:Excel Macro to copy cells in a col based on another cell

Please click on the following line and then repost your example data after reading the instructions found via that link.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 76.26%

I want to create a new column of data pulling data from one cell in a row. The problem is the particular column in that row is variable. I've created a MATCH and a new column with a number that corresponds to a cell number that ranges from 12 to 61 (or N to BJ). So, for example, I have a something like this.Column A: values 12, 12, 18, 19, 19, 25, 61Columns B through BJ have various data (numerical and text, although if its important its numerical data I am collecting)Result:BK1 = N1BK2 = N2BK3 = T3BK4 = U4BK5 = U5BK6 = AA6BK7 = BJ7THANKS!

Answer:Excel - pull data from a cell based on MATCH

Hi,You said I want to create a new column of data pulling data from one cell in a row. The problem is the particular column in that row is variableThis is confusing - you want to pull one cell in a row -i.e the cell is somewhere on a specific row.but you say that the 'particular' column in that row is variable.Does this mean that you want to create a row based on a value in a cell in a row - but the row is variable - If so what is the data or condition that allows you to select that column. You say that the column is variable but there must be a specific way to identify the column.Then you say:I've created a MATCH and a new column with a number that corresponds to a cell number that ranges from 12 to 61 (or N to BJ)Do you mean 12 to 61 are row numbers or are they columns N to BJ (which are columns 14 to 62)You need to post the MATCH() function you are using, as your description of 'creating' a MATCH function and a new column is, to say the least, not intelligible - there is no way I could recreate your formula from this description.How have you / your formulas 'created' a new column that corresponds to a cell number (does number mean cell row - or cell column and if it's a single number - where does the column [or row] value come from).You then refer to Columns B through BJ - how do these relate to N to BJ or 12 to 61Finally you show a series of values in column BK:Is column BK your 'new' column and if so what is the formula / calculation that creates the relationship to ce... Read more

2 more replies
Relevance 76.26%

Hey all,

I'm new and have no experience with Excel vba coding. Hopefully someone here can help me out.

My excel sheet keeps a list of Email addresses on column B (with duplicate email addresses), and their particulars from column C (Item price, purchase date, etc) onwards.

I need the vba to email multiple recipients (those with the "notification" field marked as yes) with their purchasing details in it. It should also prevent multiple emails to the same email address.

Thanks in advance,
Leon

PS.
Using Excel/Outlook 2010
 

Answer:Emailing multiple recipients from Excel Based off Cell Value

9 more replies
Relevance 76.26%

Hi guys,
I need to write a macro that is connected to many buttons, one for each row in spreadsheet, ask for a value to subtract, error check and (if ok) subtract from a corresponding value in column P.

When the button on a left hand side is clicked:
a dialog button should pop-up prompting a user to input the number of pieces to subtract from the corresponding cell (row based on the row of the button, column P)
error check the input number (show error if the value is smaller than 0 or larger than the corresponding value) ... I can write the error check by myself, so no need to do this step
subtract the number from the corresponding cell (i.e. user clicked a button located in cell A9, input 9,24 pieces in a dialog box and there are 15,97 pieces in P9 => the pieces in P9 should change to 6,73)
Thank you for helping me out!
Hope, the description is not very confusing.

Michal
 

Answer:Excel VBA: Subtract from a corresponding cell based on button placement

9 more replies
Relevance 76.26%

Here's an example of similar data:Name Count Column3 Column4A 2 50 51B 1 40 41C 0 1 2D 3 52 55What macro could I create that would produce this data?Name Count Column3 Column4A 2 50 51A 2 50 51B 1 40 41D 3 52 55D 3 52 55D 3 52 55Put into words, what macro could I create that would repeat each row the number of times contained in Column B?

Answer:How can I copy rows based on cell values in Excel?

Sub CopyByNumber()
'Determine last Row with data in Column A
lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop throught rows in reverse order
For srcRw = lastRw To 2 Step -1
'Determine how many rows to copy and insert
copyNum = Cells(srcRw, 2) - 1
'If Column B was 0, delete the Row
If copyNum = -1 Then
Rows(srcRw).EntireRow.Delete
End If
'If Column B was not 0, Copy and insert
For copyRw = 1 To copyNum
Rows(srcRw).Copy
Rows(srcRw).Insert
Next
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 76.26%

Hi all, long time reader, first time poster. I searched around and have found similar queries to what I require however I haven't been able to adapt these solutions with any success so here we go....

I have a list of businesses in a spreadsheet with information such as name, phone number, e-mail, website, postcode etc. I would like a search box on the side, say in cell O2 where a user can input part or all of a postcode e.g. (LS19 or LS) and those that do not begin with LS would be hidden. The postcodes are in column G.

I'm trying to make this as simple and user-friendly as possible so employees can find businesses in a certain area with ease. Thanks in advance for any help and please feel free to ask questions.

Chris Needham
 

Answer:Excel VBA : Hide rows based on cell input.

Seems nobody had the answer so I found a work around myself. Just in case anybody else wants to do a similar thing. Using auto filter is the easiest way.
Code:

[SIZE="3"]Sub Search()
Columns("G:G").Select
Selection.AutoFilter
ActiveSheet.Range("$G$1:$G$999").AutoFilter Field:=1, Criteria1:="*" & Range("O2").text & "*", _
Operator:=xlAnd
End Sub[/SIZE]

Where G is the column to search for and O2 is the users input data.

Guess this can be closed now, thankyou all for your help.
 

1 more replies
Relevance 76.26%

A user has a spreadsheet that tracks the current miles on the vehicle, has another column that shows when preventative maintenance needs to be done.What they want is to turn the current miles cell red when it is 90% to needing the preventative maintenance so it's easy to pick out of the list.I know I need to use Conditional Formatting, Highlight Cells Rule and then More Rules in Excel 2010 and also that I should choose "Use a formula to determine which cells to format" but am not sure what formula to use. Please provide examples for the formula.Here is an example of the mileage information:Current mileage 50,959 Preventative maintenance due at 51,115Thank you in advance for your assistance

Answer:excel 2010 change color of a cell based on th

Try this:
A B
1) Current Milage Maintenance Due
2) 46003 51115
3) 50959 51115

Conditional Format Column A using the formula: =IF((A1/C1)*100>=90,TRUE,FALSE)Cell A2 should remain your default color (White)Cell A3 should turn RedMIKEhttp://www.skeptic.com/

7 more replies
Relevance 76.26%

I want a cell to turn red if i input S or Sam or Spm in it, can anyone help please
 

Answer:Excel - Change cell colour based on text

Welcome to the forums . You can use a formula conditional on the Cell. Right click the cell > Format > Conditional Formatting and then use the following formula (i.e. Cell A1) and select Red:

=if(or(A1="S",A1="Sam",A1="Spm"),1,0)
 

5 more replies
Relevance 76.26%

Hello, I am working on a spreadsheet to help employees quickly calculate tank volumes. In short, we keep track of tank volume by height in 16ths of an inch. I have 10 different worksheets containing the height and volume data of the 10 tanks. The sheets are named by tank number. So for example, tank 2301 is in worksheet "2301". In another reference sheet I plan to hide, In B column have the heights in 16ths of an inch. In C column I have the volume associated with that height. I do have a Vlookup formula that works right now but I would like to use one master sheet and need to be able to enter different tank numbers in a selected cell and have the formula reference the data sheet based on the tank number we enter. Each tanks volumes are different which is why this is needed. This is the formula I currently use and it works! Just not sure how to alter it so I can have a cells value determine the reference worksheet in the vlookup formula.

=VLOOKUP($B$3,'2301'!$B$2:$C$519,2)

Id like the 2301 value to change based on what tank number we type in which will also be the data sheets name to be referenced. Maybe adding a drop down selection would even be a better idea than typing the number in.

Can this be done?

Thanks in advance for any info!

Joe
 

Answer:Excel Vlookup formula based on cell data

Welcome to the forum

Hmmm that's a tricky one. So you want the table array to be dependent on the data you type in? I think you can use the INDIRECT function for that (with the function pointing at the data input cell), but you would need to give defined names to all the relevant ranges. For example, you could name the range '2301'!$B$2:$C$519 to something like Tank_2301.

You could use Data Validation to create a drop down list to select the right tank.

How does that sound?
 

8 more replies
Relevance 76.26%

Hello

I need to do the following in VBA:

If cell in column C contains the value GBP, change cell S from that row from 13 to 23
or
If a cell column C contains the value USD change cell S from that row from 13 to 33

exception cell: C1 --> contains the title of the column

How do I do this?
 

Answer:Excel 2003 replace macro based on value in other cell

6 more replies
Relevance 75.85%

I have searched and read the topics on this, however I am apparently below beginner and need someone to walk me through the entire process. I need an email sent to myself and 2 other co-workers one day before an item listed in my spreadsheet expires. I have the expiration dates are listed in column F. I have not entered any email addresses yet.I would like, as I have read in previous threads, for the system to open and check itself daily. Any help is greatly appreciated, and I will need a very basic, step by step hand holding description.
 

Answer:Excel Email Reminder

Hi, welcome to the board.

I suggest the following link, there enough information and samples there too.

http://www.rondebruin.nl/sendmail.htm
 

2 more replies
Relevance 75.85%

I am trying to create a code that will highlight an entire row the day before the date entered into a cell on that row.
 

Answer:Excel/Highlight Row Based on Date

use conditional format
add a formula

=cell with the date = today()+1
that will be true if the date

just to make sure we understand

today() is 7th August
the date in the cell is 8th august
so you want it highlighted

so cell = 8th
today + 1 = 8th
it highlights

now to highlight the row
use a $ on the cell that has the date and apply to the whole row

so if the date is in column C and say row 5

then
$C5 = today()+1

in the conditional format formula and apply to
$A$5:$Z$5
or whatever column you need

a sample spreadsheet would be useful , you can attach here
 

1 more replies
Relevance 75.44%

Hi folks, not sure if this is possible or not but I'll try to explain what I'd like to do.

On the first sheet, I have a simple sheet to be filled in daily. The date is changed, and then daily pick figures and hours picked are updated by the relevant department.

I then have to manually put all of the data into the second sheet, which acts as a log of previous pick figures.

Is there a way the second sheet can automatically be filled in when the date is changed and new figures are put into sheet 1?

I hope I've explained this well enough, I'm trying to teach myself excel as I'm going along! Thanks.

Edit - I've had to delete peoples names' for obvious reasons, initials in sheet 2 will correspond to people in sheet 1.
 

More replies
Relevance 75.44%

Hi - I have a cell that is calculated based on 2 other cells=IF(F2="","",TEXT(F2-C2,"h:mm"))What I am trying to do is get the cell G2 to change colour if the time is => 4 hours. I have read for hours but everything suggested does not appear to workDoes anyone have any ideas of how I can do this?

Answer:Excel 2007 conditional format cell based on time value

I am assuming that the formula you posted is in G2.I am also assuming that you have entered a time value in F2 and C2.If that's the case, then this CF Rule on G2 should work:=HOUR(G2)>=4Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.44%

I am using Excel 2010

I want to limit a validation list based on the contents of Cell D4
There are 4 lists possible based on the contents of D4:

If cell is 1 = list (on datasheet tab) should be limited to range A42:A71 only

If cell is 2 = list (on datasheet tab) should be limited to range A72:A87 only

If cell is 3 = list (on datasheet tab) should be limited to range A88:A90 only

If cell is 4 = list (on datasheet tab) should be limited to range A92:A93 only
Thanks
 

Answer:Excel 2010: Limit validation list based on another cell

Soooo... is this possible or no?
 

3 more replies
Relevance 75.44%

I would like to know a method to solve an otherwise repetative task in Excel for me.

In my Excel spreadsheet, I have many columns relating to customer orders etc. Each customer (finite number (~40)) has its name (customer1, and customer2 etc) input to a cell, I then must fill out the customer account number (ac001, ac002 etc). The customer name and number already exist on another sheet.

How can I automate the process of when I input the customer name, excel then fills in the customer number in the next cell?

Many thanks
Dave
 

Answer:Excel: Conditional Cell Input Based on Entered Data

Depending on how your Customer data is arranged, look at either Dlookup or Vlookup.
But Access is better for Customer databases.
 

3 more replies
Relevance 75.44%

Hi,
I am having data structure as below. I need some macro or some means to populate formula in Column D based on column C (as and when it changes) . All other column data are populated. This is beyond me. Any help or direction is greatly appreciated. Thanks in advance

Col | A | B | C | D | E
------------------------------------------------------------------------
Row | 1 | Test1 | | | 10
| 2 | Test2 | =A1 | =MAX(E2) }| 15
| 3 | Test3 | =A2 | =MAX(E3) | 20
| 4 | Test3 | =A1&","&A3| =MAX (E1,E3)| 30
 

Answer:Excel macro or formula - change field based on other cell

7 more replies
Relevance 75.44%

I have an excel sheet with two tables for customers to know which products the customer is using based on a Contract table.

The enclose sheet has two tables:
1. Customer
2. Contracts

All what I need is to fill the columns of "Product A", "Product B" and "Product C" of every customer looking at the table "Contracts" where the data is available based on the "Customer ID"

How can I do it?
 

Answer:Solved: MS Excel / Filling a cell with YES based on data from another sheet

I would just use a simple countifs formula to check if the customer ID and Product type match (ie the count of matches is greater than zero)
See attached.
 

3 more replies
Relevance 75.44%

Hi all,

Complete newbie here, have been reading various posts and trying to figure things out on my own but to no avail. Maybe someone can shed some light on my problem.

I'm using Excel 2004 (Mac) and want to create a VBA/macro that would change the background colour of specific cells (say A4, A5, A6 and C8) based on the number OR letter in the cell. There is a drop-down from which the user must select either 0,1,2,3,4,5 or H for these cells. I want the colour to change based on the user's selection as follows:

0 or 1 = Red (i.e. colour value 3)
2 or 3 = Yellow (i.e. colour value 6)
4 or 5 = Green (i.e. colour value 4)
H = Lavender (i.e. colour value 7)

I've found sample event code showing how to change the cell colour based on a numerical value/range, as well as different sample code showing how to change the colour based on the letter/text, but not *both in one*. (I've found out how to to insert the code via View Code when clicking the worksheet tab but am unfortunately not savvy enough to know how to set up the code correctly).

I can't imagine it would be too complex for someone with a decent level of VBA/macro knowledge. BTW, I would be using conditional formatting to do this except it only allows for 3 conditions/colours, whereas I need 4! (thanks MicroSoft...)

Any suggestions on how to do this would be very much appreciated!
 

Answer:How to change cell colour in Excel based on number *or* letter?!?

Technically, you can still use conditional formatting in this case - you have 4 cases and 3 max options. So just make the default background Lavender, then then use the 3 conditional cases to change it to Red/Yellow/Green. As it's a dropdown, this shouldn't break anything and selecting H will leave the default lavender colour. Not ideal, but perhaps a simple macro-free option.

If you want to use VBA then you can use code like this:

http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm

This page explains how the Case statement works for number and letter selections (near the bottom):

http://www.ozgrid.com/VBA/select-case.htm

Hope that helps
 

6 more replies
Relevance 75.44%

i have a spreadsheet that updates with data from a number of different sources. i need to filter only the relevant data that i need. i'm almost there but need to find out how to pull 2 more pieces.

in the attached spreadsheet the cells i need are in column R and T. I need to know the corresponding "name" and "size" of the data that is in column S.

unfortnuately the actual values don't show up in the spreadsheet b/c they are live data feeds but the formulas written up until now are still there.

in a nutshell what i'm doing is getting 5 price feeds and i need to filter out which is the highest updated within the last 2 minutes. i actually have the only the price figured out in column S, but i also now which name and how big the size is related to that price.

your help is very appreciated.

regards,
 

Answer:Solved: excel - data returned based on the value of a specific cell

10 more replies
Relevance 75.03%

Hi guys,
I have a list of some certifications and their respective expiry dates written in Excel sheet. What I want is to be getting Auto reminder few days before, on and after the expiration date.
Attached is the VBA program that I tired to use but it was reading the whole data instead of being selective. Also attached is an extract of the excel sheet.
Pls I will appreciate any help
Thanks,
CJ
 

Answer:Auto Email reminder from Excel

Have you read the Thread under yours -
http://forums.techguy.org/business-applications/856705-automatic-email-excel-based-date.html
 

1 more replies
Relevance 75.03%

Hello there,

First time posting and very average excel capabilities... Working on generating a code that will send automatic email reminders 7 days prior to the date within rows F-P. Recipients contact information is in column S.

Thanks for your time.

Tim
 

More replies
Relevance 75.03%

I have a real simple need. Well, it's probably simple for some. I have a sheet built to track employee hire dates and eval dates. I would like to assign excel to open everyday at a time and then evaluate the cells with the eval and raise dates to see if an email needs to be sent to the supervisor. If so a simple "there are employee items coming due" email is sent to the supervisor. The super will go to the document and take care of the rest. Dta is not real. The email with the employee name isn't the one I'll be sending to. The Team Manager at the end is where the email will be going to for all on the sheet. I would appreciate the help. Running windows 7 and office 2013.
 

More replies
Relevance 75.03%

I have a list of task (maintainance schedule) to do... and they are required to be done in a by certain different dates regularly (eg. every 14 days or every 28 days)

I have a specific maintance date for each task within the same row. Is there anyway i can make it to automatically send an email to remind me or another person a day before it's due??

thanks
 

Answer:Set email reminder base on Excel

16 more replies
Relevance 75.03%

Dear all,I am now doing a proj on excel application !Could anyone help me about this ?Actually, i know nth about the EXCEL VBA, but i have to generate an auto email based on the details of Excel. The followings are the situations.I have to email the "Job Title" in the cell to a particular person (according to the cell) automatically based on the cell dates.It is something like Email alert.Here is the sample of my excel workseet1 Job date 1 date 2 resp. by email2 job 1 dd-mm dd-mm john [email protected]

Answer:email msg in exl on cell date to certain ppl

Hi,Look at this post, response 7 andthis post.Both create and send e-mails from Excel.Note the warnings about the use of passwords in the code and potential security risks - less of an issue if you are not distributing the worksheet to anyone else.Regards

5 more replies
Relevance 75.03%

hi, i'm trying to get a cell in excel (d2) to automatically fill, based on a value in another cell (a3). e.g: if a3 has a value of between 2-5 den d2 will display 100, or if a3 has a value of between 6-9 den d2 will display 105. if someone could please help me, thanx!

Answer:Excel - automatic cell fill

Hi,This formula in cell D2 returns the values you gave in your example:=IF(A3<2,"",IF(A3<6,100,IF(A3<10,105,"")))The formula first tests if Cell A3 is less than 2 and if it is less than 2 it shows nothing "". It then tests to see if A3 is less than 6 and if it is less than 6 it shows 100, if it is not less than 6 it tests A3 again to see if it is less than 10 and if it is less than 10 it shows 105, but if it is not less than 10 it shows nothing. Using the IF() function multiple times one inside another is known as a nested IF.Regards

3 more replies
Relevance 75.03%

I need help please. I need to create a basic calender which shows the month on the top and when you click an arrow the month will change and a set of cells underneath will change. I intend to write into the cells details and when i click on the arrow to change the month the data will disappear. when I click on the arrow to go back a month, I will need this data to reappear. I would appreciate any help. I have created the arrows that change the month.

Answer:Need to change a set of cells based on the date (on excel)

Why not just create a new tab for each month and switch to the tab for the month you are interested in?Visually you would accomplish the same goal and you could go directly to any month you wanted.How have you created arrows to change months?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.03%

Excel 2010 IF stmt based on DATE value: I have a simple employee expense report that calculates mileage in cell D12 based on the date in cell B12. My formulas is as follows: =IF($B12>=DATE(2011,1,7),$D12*0.555,$D12*0.51). All mileage incurred equal to or greater than 07/01/11 should be calculated at $0.555 cents while all else should be calculated at $0.51 cents. I believe it's as simple as Excel not recognizing my DATE(2011,1,7), as the date 07/01/11... Help?

Answer:Excel 2010 IF stmt based on DATE value

You are right, Excel will not recognize DATE(2011,1,7) as 07/01/2011 sinceDATE(2011,1,7) means January 1, not July 7.The syntax is: DATE(year,month,day)=DATE(2011,1,7)=DATEVALUE("1/7/2011") will return TRUE.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 75.03%

Alright this one might be a bit of a challenge. I am releasing a complex spreadsheet with my product pricing on it. Is there a way to make the program have a "shelf life" of 6 months or so? I would like to set it up to make it inaccessible to anyone without the password after a certain time period. So basically I would like to create a macro that will disable the entire spreadsheet (without the password) after a specific date so I can release new versions (with the new pricing) without the old versions (old prices) getting thrown into the mix. Anyone have any ideas?? Thanks.
 

Answer:Excel Macro - Date Based Protection

Hello CWDENVER, this should be possible to achieve, you can have a date held in a cell in a hidden Protected sheet in the workbook. When the System date = the date in the Cell the Target Worksheet is hidden and can only be made un-hidden by entering a password.
To be effective the Menus would need to Removed from the Workbook as well.
The VBA Editor would need to be password protected as well.
 

2 more replies
Relevance 75.03%

There is a file shared by my group where we track work orders scheduled by date. Column 1 contains a list of dates, in order, like so
5/30/06
5/31/06
6/01/06
etc.
Sometimes, when you open the file, someone else has scrolled up or down the page, so if you want to view today's date, you have to scroll up or down to find it. What I would like to do is create a macro that takes you to the cell containing today's date. I tried creating a macro that did the following:
1- Go to a cell containing a formula =TODAY()
2- Copy the value of that cell (this will be today's date)
3- Open the Edit --> Find window
4- Paste in the value I just copied
5- Click find

This worked great on the day that I created it, but today, it still goes to yesterday's date. When I look at the macro, what it's doing is always searching for the value that "TODAY" had on the date I created the macro. It's not smart enough to actually paste in the value I copied, but instead sees it as FIND: WHAT="5/30/2006"

Is there a way to use the Find or CTRL-G, or any other formula to take me to the cell containing today's date? It seems like this should be a simple thing, but I just can't figure out a way to make it work.

Thanks for any help you can offer!
 

Answer:Excel: Go to cell containing today's date

If you have a cell on the worksheet with =Today() in it, that cell will always show the current date. You look up that in your macro and use that to find your current date in the data.
 

3 more replies
Relevance 74.62%

Hello,

A pretty inexperienced VBA'er here.

I have 2 named ranges:

- LastStatus = "J8:J500"
- CurrentStatus = "K8:K500"

What I would like to do is create a loop to copy the cells in CurrentStatus over to the LastStatus column if and only if there is a value in CurrentStatus (column K). If there is no value in column K and there is a value in column J, I do not want change the value in column J.

Any help would be greatly appreciated.

Brian

 

Answer:Excel - Conditional Copy / Paste (Same Worksheet) based on cell values

You don't need a loop.

Sub test()
Range("LastStatus").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]<>"""",RC[1],"""")"
Range("LastStatus").Value = Range("LastStatus").Value
End Sub
 

1 more replies
Relevance 74.21%

Hi Guys,

I have just joined this forum, I found some solutions given by Zack quite useful to me. Thank you so much.

But also need help in taking it further, as I have created a worksheet with lots of data which will have to be incorporated while sending auto email reminders to various admin officers depending on the dates, especially for Statutary payments like Telephone, Electricity etc.

If any body can help....... Please...............
 

Answer:Auto Email reminder from Excel Sheet

http://forums.techguy.org/business-applications/773873-how-make-excel-send-email.html
 

1 more replies