Computer Support Forum

Automated emails when cells in excel are at specific date?

Question: Automated emails when cells in excel are at specific date?

Can anyone help me please?I'm trying to get excel to send an automated email from outlook when a due date comes about in certain cells Desperately seeking help on this Thanks

Relevance 100%
Preferred Solution: Automated emails when cells in excel are at specific date?

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: Automated emails when cells in excel are at specific date?

Try here:http://www.rondebruin.nl/win/sectio...should give you a starting point.MIKEhttp://www.skeptic.com/

4 more replies
Relevance 89.79%

Hi , i am working in finance and mostly with numbers .. i have two sheets the 1st sheet includes two columns [order number] and [value] order no. aren't sequentially and each order no. has unique value and both columns already filled... and the 2nd sheet have same columns but only contains a randomly group of [Order Value] and [value] column is empty and i need to fill each value of order no. from 1st sheet ...

Answer:Excel : How to copy specific cells if 2 others cells matched

Without knowing how your spreadsheet is arraigned, Column Letter & Row Numbers,the best I can offer is a general suggestion of using one of the =LOOKUP() functions,like =VLOOKUP() Somthing like:=IF(VLOOKUP(A1,SheetName!A1:B10,2,FALSE)Where cell A1 is your unique value.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 81.59%

Hi everybody,

First time poster. I currently have 9 tabs open and hours of researching trying to figure this out. I can say I'm more educated now with excel than I was this morning. Very powerful software.

I seen current thread that were very close to what I need but was unsuccessful with achieving what I needed.

I'm trying to set an email reminder sent to my team when a task has not been completed (Column D) by the due date (Column C) and some indicator on column H when completed. It would be most ideal if the subject had the part number "123453 Rev 06 Doc Control Update" and body said Dear Andy(Task Owner) reminder, please update documentation related to your department. Thanks.

I currently have a macro for 'Task completed' be filled in a green color when complete. I'll copy and paste below what i currently have. I know this is probably a long shot but I am getting somewhat stressed and losing hope with this, please please please help. function is more critical for me then the looks, if i have to move some cells around I don't mind one bit.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("D2:D100")) Is Nothing Then
With Target(1, 2)
.Value = Date & " " & Time
.EntireColumn.AutoFit
End With
End If
End Sub

Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim... Read more

Answer:Automated email in excel, driven by due date

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("D2:D100")) Is Nothing Then
With Target(1, 2)
.Value = Date & " " & Time
.EntireColumn.AutoFit
End With
End If
End Sub

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
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Sheets(1).Select
lRow = Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To lRow
toDate = Replace(Cells(i, 3), ".", "/")
If Left(Cells(i, 5), 4) <> "Mail" And toDate - Date <= 7 Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
toList = Cells(i, 4) 'gets the recipient from col G
eSubject = "Document Control Status update "
eBody = "Reminder," "Please update your project status."

On Error Resume Next
With OutMail
.To = toList
.CC = ""
.BCC = ""
.Subject = eSubject
.Body = eBody
.bodyformat = 1
'.Display ' ********* Creates draft emails. Comment this out when you are ready
.Send '********** UN-comment this when you are ready to go live
End With

On ... Read more

1 more replies
Relevance 81.18%

I resolved one problem this morning and ran into what is hopefully the final issue.

I have stations that range from 100+00 - 415+00. The top line for each station is an elevation. The bottom line beneath the elevation is an offset. I need to shift all the offsets of the number "0" in say column J, maintaining the format it is in now. Basically shifting each elevation and offset either left or right so the number 0 is in column J.

Thanks.
 

Answer:Solved: Excel: having several specific cells in one row

6 more replies
Relevance 81.18%

Is there a way to designate cells that you want to tab to in Excel. I have designed a form in Excel, but want to make it easier for the [non-Excel] users by allowing them to tab to jus the cells that need completing. Anyone know of a way?
 

Answer:Tabbing to Specific Cells in Excel

Hmmm Forms are normally better created in Word

However you could try protecting cells from being changed. For example, if you have created a worksheet to be used for data entry, you can protect all the cells that contain formulas, so that others who enter data don't change them. To stop cells from being changed, the cells must be locked, then the worksheet must be protected.

By default, cells are locked. However, if you want to stop a cell from being changed and it has been unlocked, you need to lock it. Select the cell (or cells) and choose Format | Cells. On the Protection tab, check Locked and click OK.

If you want certain cells to be editable, you need to unlock them. Select these cells, chooseFormat | Cells | Protection tab, and uncheck Locked.
Click OK

Choose Click Tools | Protection | Protect Sheet.
In the Protect Sheet dialogue box, you can choose to protect contents (of cells, including chart sheets), objects (graphics, formatting, comments), and scenarios
Uncheck any of these three options if you don't want to protect them. To protect cells from being changed, keep the Contents option checked.

If you want to require a password to unprotect the worksheet, add it in the Password text box. Don't forget the password! Passwords are case-sensitive -- to use it you must type it exactly as it was entered.

Retype the password when prompted.

Click OK.
 

1 more replies
Relevance 80.77%

I have a sales report that automatically updates daily. Is there a way to setup automated emails from excel to different individuals? It would save so much time and effort. Any help is appreciated!
 

Answer:How to send automated emails from Excel?

There are quite a few similar threads with sample files and more.
Check these out.
There are two I helped with, one on October 13 was asked by vasu0505 and one from June 22 placed by truec
Search for posts by these posters and you'll come across them,

With the information you have given (Excel version missing) and no data explaining it's an open guess and guessing is no option

When you open this post similar threads are displayed below so you can directly click them.
Happy hunting
 

1 more replies
Relevance 80.77%

HiI have a few worksheets with expiry dates, I was wondering if there was a way to set up an email reminder when due dates are approaching - and then again when due dates have passed.all of my due dates in are in column D, but not every row, they skip rows if that means anything. I just need the emails to come to me, not to everyone these dates belong to.(I've already read these, but they didn't help me - TRYhttp://www.rondebruin.nl/sendmail.htmMIKEhttp://www.skeptic.com/)Thanks very much for any further help

Answer:sending automated emails from excel

I have something similar that i use at work, but i have since re created this in VB6 and therefore cannot remember exactly how i did this in excel, but saying that here is something i would probably do.1) Your due dates are in column D2) Use a free column for todays date '=Today()'3) use another column to calculate the days between today and you due date for example1 D E F
2 30/09/2013 02/09/2013 28
in this example D is my due date, E is todays date, and F is the difference in days between the two, so i would make macro to go through all the records and then send an email when F is for example 10 days. If you wanted to calcualte the dates difference dynamically you can use the DateDiff or even DateAdd function in VBA. You can then use the below code to generate an email which will automatically be sent.Sub Mail_Workbook_1()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hello World!"
... Read more

2 more replies
Relevance 80.77%

hello,

I need some help in figuring out how to send an automated email message from an excel spreadsheet to my outlook. My basic requirement is that when the value in a cell ( in my case it is the number of left over days until the deadline) I require an automatic email to be generated and sent to me when the value in this cell reaches a certain threshold value.

thanks
 

Answer:Help with sending automated emails from excel

Welcome to the forum.
If you do a search on your subject you'll finde several posts with the same question. I think the results can help you.
 

1 more replies
Relevance 79.95%

Hello, I need to figure out a macro to select certain ranges of cells depending on what is in cel 'A1'.

I have a column of dates in column 'B' and two columns of numbers in 'H'. I want to be able to enter a date into cell 'A1' and have it find that date and then select all cells in column 'H' that correspond to all dates before and including the date entered in cell 'A1'.

eg.

A1 = 1/1/2010

B2 = 11/12/2009 H2 = 2.56
B3 = 15/12/2009 H3 = 30.99
B4 = 20/12/2009 H4 = 32.54
B5 = 25/12/2009 H5 = 5.65
B6 = 31/12/2009 H6 = 3.54
B7 = 1/1/2010 H7 = 6.87
B8 = 20/1/2010 H8 = 1.25

Since A1 = 1/1/2010 I want to select all cells from H2 to H7

I do not need this to be done automatically as I will push a button to activate the macro I just need the macro to select the correct cells when i push the button.

Can someone please help me figre this out I am a complete newbie when it comes to Macro coding, but I do understand the rest of Excel very well.

Thanks.
 

Answer:Excel Macro to select specific cells

7 more replies
Relevance 79.95%

I am attempting to hide specific cells based on input into a cell and am having some trouble touching on the correct code.

I working on a grading form for a class that I teach and would like comments to be revealed based on a corresponding score. For example, scoring a specific from 0-4, if a 4 is entered in A1 a perfect comment will be revealed in the cell immediately to the right of A1. If a 3 is entered in A1, a new comment will be revealed in the cell immediately to the right of A1 and so on.

Below is the code that I currently have (which is not working) that may help give a better idea of my intention for the worksheet.

'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Dim cell As Range
'If Range("F24") = "3" Then
' Range("H25,H26,H27,H28").EntireRow.Hidden = True
' End If
'If Range("F24") = "2" Then
' Range("H24,H26,H27,H28").Hidden = True
' End If
'If Range("F24") = "1" Then
' Range("H24,H25,H27,H28").Hidden = True
' End If
'If Range("F24") = "0" Then
' Range("H24,H25,H26,H28").Hidden = True
' End If
'End Sub

Any help will be greatly appreciated! Thank you so much!
 

More replies
Relevance 79.95%

I need an excel formula that checks a specific number of rows (let's say A1:Z1). If the cells have data, I want to add 5 for every cell that has data (it doesn't matter what the data in the cell is. Just as long as it has data). So if 10 cells have data, I would get a value of 50.

Is this possible? I'm having a brainfart and can't figure out how to do it.

Thanks.
 

Answer:Excel Help - return specific value if cells contain data

How about
Code:
=(counta(a1:z1))*5
Seems to work ok on a test. This will count cells with any data in it.
 

2 more replies
Relevance 79.95%

Good morning.Is there an easy way to alter the font colour of specific cells in Excel?I need to run searches through phone bills & it would make it a lot easier if I could automatically highlight certain phone numbers (for expense claim - not spying on my family!)ThanksWint

Answer:Auto colour specific cells in Excel

Yes, it is called Conditional Formatting.Put this into the Help box in Excel and come back if you need more specific help.

2 more replies
Relevance 79.95%

Mr. Database created the macro in this spreadsheet and it works great. I need to know how to update the macro when I need to add rows or columns.
 

More replies
Relevance 79.95%

I know variations of what I want to do exist but I have not had success in implementation. I am using Excel 2007 and MS Outlook.

After opening the file, I would like the macro to look at all the dates in the sheet and send an email (.Display is preferred) each time a date gets within 1 year, 6 months (180 days) and 90 days of the current date.

The headers in the columns are:
A-First Name
B-Last Name
C-email address

D, E, F, G, H, I, J-Certification 1, 2 ,3 etc. (All contain various expiration dates)

K-1 Year email sent. I've gathered from reading that another column indicating if an email was sent is a good idea and may be needed to trigger the other events.
L-180 Day Email Sent
M-90 Day email sent
 

More replies
Relevance 79.13%

Hey Guys

I've got a list of figures - and I need to make a specific answer (171.74 in this case). But i've got a big list of figures - and I'm not sure what figures add up to it, because I get a lot of these - is there an excel formula or technique which would do it automatically, rather than a lot of manual trial and error?

Thanks

Jay
 

Answer:Excel - Adding Different Cells to meet specific answer

7 more replies
Relevance 79.13%

Hi

I searched the forum and I read through similar requests but not exactly what I am trying to achieve--I think!

I have a word template (letter) where I want to include data from an excel workbook (wb). The excel wb has several sheets. The sheet named "cover" has the majority of the data that I want in the letter. For example, I want c21, b58, d3 and so on. The wb is NOT setup like a database with columns and headings. There are 100 individual wb's saved to a folder with unique names.

I have probably made this harder than it is but I do not know what commands I need on the word template. I need the flexibility of accessing folders and several wb's with unique names.

I can include some samples which would make it clearer.

More replies
Relevance 78.31%

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 10 Home, 64 bit
Processor: Intel(R) Core(TM) i3-3217U CPU @ 1.80GHz, Intel64 Family 6 Model 58 Stepping 9
Processor Count: 4
RAM: 3981 Mb
Graphics Card: Intel(R) HD Graphics 4000, -2041 Mb
Hard Drives: C: Total - 454670 MB, Free - 385845 MB;
Motherboard: ASUSTeK COMPUTER INC., S500CA
Antivirus: McAfee Anti-Virus and Anti-Spyware, Disabled

Hi

I am new to this forum--I read several posts similar to my topic but was unable to adjust to my specific request.
I have a workbook (wb) in Excel with several sheets. The excel wb is designed as a form where users are filling in information. The worksheet named "Cover" has several fields or cells (example: d58, c21, a5, etc. ) where I want to use that information in a word template. I have 100 wb's filled-in with unique information from each applicant. The excel wb is NOT designed as a database with columns and column headings. My goal is to produce 100 approval letters pulling in the data from each Excel wb. This task is preformed where the majority of the letters are generated in October. This task is repeated for each new fiscal year. I would need flexibility with folder names and excel names. Currently I have a word template that I manually type in the information from each wb. If I could automate the task it would decrease errors and time. I hope I am clear and I can provide the files if necessary.
 

Answer:Word Template pulling specific cells from Excel form

Hi, welcome to the forum.
Yes it can be done, but keep in mind that you will need a basic consistency, all workbooks must have the fields in the same place.
I think the only way you can make this workable is to create a macro-embedded word file where you are forced to select a folder and maybe a excel file selection with a wildcard if more that one file is present in that folder.
I suggest you include the version of Office you are using e.g. 2010 or newer
 

1 more replies
Relevance 78.31%

I have an excel worksheet in which a column contains some cells in which the monetary values are coloured blue.

Is there some function I could use to sum only these coloured values ?

I have been told to use the SUMIF function , but I am unsure how to use it with respect to coloured fonts.

Suggestions most welcome.

Regards
 

Answer:Excel 2003 :Summing specific cells of a particular color in a column

7 more replies
Relevance 78.31%

Hi

I have a following macro:
Code:
Sub BondPrices()

Selection.AutoFilter Field:=16, Criteria1:="BONDS"

Range("S2:S10000").Select
For Each cell In Selection
If Not IsEmpty(cell) And IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
cell.Value = cell.Value / 100
End If
Next cell

Selection.NumberFormat = "0.00%"
Selection.AutoFilter
End Sub
The problem is that every cell in the sheet gets divided by 100, not only the filtered cells (only BONDS).

Any help would be greatly appreciated.

Michael
 

Answer:Solved: Excel: Macro to divide specific cells by 100 with a filter

I've solved the problem myself by adding a line to the code that selects only visible cells:

Code:
Selection.SpecialCells(xlCellTypeVisible).Select

 

1 more replies
Relevance 78.31%

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

Answer:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 77.9%

I would like to format excel cells with conditional formattingIt is a column of dates and I'd like to determine the following If the date in the column is less than today +30 daysIs the formula i'm trying to make, but it does not work =/ Any assistance would be greatly apprecited.if = (today() < +30)

Answer:Excel: If Date is within 30 days format cells

How you want to format? what is "=/"?

4 more replies
Relevance 77.9%

Where to start?

I want to be able to click on a blank cell, and when I click on it I want it to add todays date without having the user go through the formulas. If there is a date on there from the previous time it should change it to todays date.

It would be nice to have a default words in the cell example-
"Click here to insert date"

I'm kind of fimiliar with VB and had a semester of C++ which doesn't mean anything other than I can follow along.

Any help and suggestions welcome
 

Answer:Auto Date on cells Excel 2000

Can you just put =now() into the cell? That gives today's date when you format the cell to show just the date (and not the time). It will always show today's date, no matter what date it is today.
 

3 more replies
Relevance 77.9%

Hello, I am in an Army Airborne Unit and I am the Jump Log custodian. It is a requirement that soldiers Jump every 90 days. More important the Jump Master must perform their duties every 180 days. I would like to build a spreadsheet that tracks this I would like to keep it simple so Green for when they are good and gradually turn red when they are out of currency. Here is an example of what I have, Name, Date of the last time duties where performed and the date that they must perform the duties to stay current. I put a formula in Colum C that adds 180 days to the adjacent date in Colum B. I would like to see the soldiers name turn red and I know there is a way for the cell to turn colors gradually using the three color option. Jump Master Performed Duties On: Must Perform Duties By:
Corley 4-Sep-13 3-Mar-14
Lindsey 4-Apr-13 1-Oct-13
Adkisson 1-Oct-13 30-Mar-14
Evans 22-Feb-13 21-Aug-13
message edited by Steven720

Answer:Change a cells color in Excel by a date.

I would like to see the soldiers name turn redThis is for Excel 2007, see if this works for you:With your data like:
A B C
1) Jump Master Performed Duties On: Must Perform Duties By:
2) Corley 04-Sep-13 03-Mar-14
3) Lindsey 04-Apr-13 01-Oct-13
4) Adkisson 01-Oct-13 30-Mar-14
5) Evans 22-Feb-13 21-Aug-13
There are Four separate formulas, and they must be in the correct order, so after your done entering all the formulas they should be, in the following color order:10 Days = Red30 Days = Orange60 Days = Yellow90 Days = GreenFirst Formula: 1) Select your cell or Range of Cells, A2 - A5 (Names) 2) On the ribbon click Conditional Formatting 3) Click on New Rules, it?s near the bottom of the dialog box. 4) Click Use Formula to determine which cells to format. 5) Enter the formula: =C2-TODAY()<90 6) Click on the Format button 7) Select the Fill Tab 8) Select a Green color 9) Click OK10) Click OKSecond formula: 1) Select your cell or Range of Cells, should be the same as above. 2) On the ribbon click Conditional Formatting 3) Click on New Rules, it?s near the bottom of the dialog box. 4) Click Use Formula to determine which cells to format. 5) Enter the formula: =C2-TODAY()<60 6) Click on the Format button 7) Select the Fill Tab 8) Select a Yellow color 9) Click OK10) Click OKThird Formula: 1) Select your... Read more

7 more replies
Relevance 77.9%

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 77.9%

Hi All
i'm newbie and as the title implies, i want to get my emails through pop3 with outlook express BUT Specific date only !!
Could anyone tell me how i can do this?
Best Regards.
 

Answer:OutLook...How to recieve only emails with specific date???

why not take them all and then delete the dates you dont want
I dont think you can set up date specific pop - I know you cant on gmail - the choice is all or from now on the pop page settings

it would be easy to sort by date on outlook express and just delete the emails

whos the email ISP with ?
 

1 more replies
Relevance 77.49%

Hi,

Starting from row 4:
I need the code to copy down the first name,
then starting from column g to cd in that row, it should copy down the mark for a subject (if there is a mark under that subject heading), otherwise skip that that subject.
also copy down that subject's, subject code, which is in the second worksheet.

In summary, If mark under subject for that person, look at subject, compare against code list, copy subject's code, then copy the mark.

Copy down any comment for that person.
Further details in the attached file.

Maybe a loop function or something?
Im very unfamiliar with VB so all i can really do is copy and paste code.

Any help at all would be appreciated.

Thankyou!!

UPDATED

NOTE: I have also cross-posted in another forum, so any updates should also be checked there:

http://www.excelforum.com/excel-pro...-within-a-specific-range-to-notepad-file.html

http://www.mrexcel.com/forum/showthread.php?p=1942476#post1942476

Cheers

 

Answer:Excel - How to export only non-empty cells within a specific range to notepad file?

6 more replies
Relevance 77.08%

I need to work out a formula that will allow me to count the number of products despatched between two dates. The dates are in cells A1 and B1, and the other cells with dates of despatch in are in cell C1 to C100. I want to have another cell which basically counts the number of items despatchedbetween the dates in A1 and B1. Can anyone help?
 

Answer:Count number of cells with value between date range - Excel

I'm sure there may be a better way to do it but one, quick and dirty way would be to use column D with a formula that tests each "C" cell to see if it falls between A1 and B1. If it does, have the formula print an "X" and then simply count the "X's".

D Column Formula: =if(and($c1>=$a$1,$c1<=$b$1),"X","") -- Put formula in cell D1 and drag through D100
Cell D101: =countif($c$1:C$100,"X")

A simpler way could also be to put a formula in cell C101: =subtotal(9,$c$1:$C$100) -- After you place the formula, you then apply a filterto cells C1:C100 testing for greater than/or equal to A1 and testing for less than/or equal to B1. Apply the filter with the C1 through C100 highlighted and view the "COUNT" in the status bar.

I hope this helps!
 

3 more replies
Relevance 76.67%

Hi all,

I really need help with this, as I have tried means to get it right, but I have no experience in VBA and it seem difficult. Any help will be greatly appreciated.

I need to copy specific cells in a form (in Excel format) that is input by users and paste them into the summary workbook.

The form is fixed, but every time someone sends in a new form, I'll need to update the data in a summary workbook. So it'll be constant updating and I need to ensure that a new row in the Summary folder is used for each form that is sent in.

I'm trying to write a macro that is able to automate the data transfer.

Eg. I need to:

copy the data from D6 in the file Form to the celll A2 Summary file,
D7 in Form to B2 etc.
Attached is the form and my summary sheet.

Thanks in advance!
 

Answer:Help Needed for Excel Macro - Copy specific cells and paste into a Summary workbook

Hi all,

Below is the vba code that I have written.
But the problem is I am not sure how to define the destination workbook as a file, which is also where this macro will be stored.

It does not seem to work when I tried to put ThisWorkbook or Activeworkbook. I did not want to add a new workbook either.

Sub copyWorkbooks()
Dim MyPath As String
Dim SourceRcount As Long, FNum As Long
Dim mybook As Workbook, DestWks As Workbook
Dim sourceRange As Range, destrange As Range
Dim rnum As Long, CalcMode As Long
Dim SaveDriveDir As String
Dim FName As Variant

' Set application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
SaveDriveDir = CurDir
' Change this to the path\folder location of the files.
ChDirNet "C:\Documents and Settings\chinba\Desktop\ASL Exception\"
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
MultiSelect:=True)
On Error Resume Next

'find the last row
RDB_Last = rng.Find(What:="*", _
after:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
If IsArray(FName) Then
'Loop through all files in the myFiles array.
For FNum = LBound(FName) To UBound(FName)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(FName(FNum))
On Error GoTo 0
If Not mybook Is Nothing Then
On Error Resu... Read more

1 more replies
Relevance 73.8%

This is a bit tricky to explain:

I have dates along the X axis and quantities up the Y axis.

When I enter a quantity on a date I need a formula to calculate
a) 3 times the entered quantity and then again 6 times the entered value (easy)
b) for these two values to automatically land on the 6th and 12th month from the date of entry (not so easy)

I need help with (b)

I'm picturing this as an ongoing line graph with new series being added periodically.

Is this possible or am I making this unnecessarily difficult for myself?

Thanks

Corinne
 

More replies
Relevance 72.98%

I am trying to build an excel sheet to tell me my future manning levels, (for example, how many workers will I have 6 months from now). I attached (or at least tried to) a very small scale "example sheet" of what I am talking about. It basically will have one cell to put a start date in for which month to start gathering data. This is followed by the months following the start date going out a year. The data comes from a separate sheet and the data is based off of a column with dates that I am suppose to receive inbound and a column with dates of personnel departing. I am trying to take the month that I need the data for, count the personnel that should have arrived by that date and add it to those that have not departed by that date. Every formula that I have tried counts every cell to include the empty ones. This is the last formula that I tried =SUM(IF(Manning!$C$3:$C$14<=H3,1,0))+SUM(IF(Manning!$D$3:$D$14>=H3,1,0)). I have also tried COUNTIF formulas. How do I get it to count only the cells that have a date in it that falls with in the range? Sorry if it is confusing but hopefully the attached sheet will help explain. I am using an XP machine.
 

Answer:Solved: Using excel to count a specific date range

I put in a check to put in a ZERO if there is an empty cell.

=SUM(IF(Manning!$D$3:$D$14<=H3,IF(Manning!$D$3:$D$14="",0,1),0))+SUM(IF(Manning!$F$3:$F$14>=H3,IF(Manning!$F$3:$F$14="",0,1),0))
 

3 more replies
Relevance 72.98%

I am stuck at a problem where I want to export emails with specific "TO" ( can be a Distribution List). And I expect the excel to have the email address of the sender and the time at which the email was received. Is there a way to do this? I have looked on several forums and sites, but unable to find something like this.My ultimate goal is to track the emails coming in my Microsoft outlook sent to me Distribution List and export them in an excel sheet. The objective is to look for the number of emails recieved and the time/date at which it was received.Note: I am using an IMAP account on my outlook and there is no exchange server.Any help is great appreciated!

Answer:How to export specific emails to excel?

Create a folder for the emails. Then, create a rule that moves emails sent to that list to the folder. That's how you can track them. No need to do extra work of exporting them when the information is right there in Outlook.id10t

6 more replies
Relevance 70.93%

HI guys,

I am using Microsoft Excel 2003 and am trying to create a scheduler for my work place.
What I have to do is allow the user to enter data in the input sheet (by input meaning Dates) and then accordingly scan the schedule sheet and find the date entered on the input sheet, following which print the appropriate schedule opposite that date for that particular project.

For Example .....
If a person is working on a project called apple in cell D (effectively making it the 3rd project in the list) and the user enters the project start and end dates as 5th June and 13th June. So what I have to do is scan the Schedule sheet for these dates and enter the appropriate text of Start and End before these dates in the D column (i.e under the respective project).

How to do this I am clueless as I am a complete idiot as far as excel is concerned. SO any idea or any sort of help as to how this can be achieved will help.

The sheet must be automated as far as possible and there are about 25 to 30 projects which need to be updated into the sheet before the final output.

Thanks in advance.
 

Answer:Solved: Formula to find a date and enter schedule in a specific cell in Excel

13 more replies
Relevance 70.93%

hi there,
i need to know if you can 'bind' a group of cells to specific 'search' cells?

i have a worksheet with records on tyre stuff, serial #, make, and so on. the problem is i need to be able to sort the records according to eiter the serial # or the make, and still keep it together with the historical entries below it.
i hope i'm making sense lol.
i'm hoping someone can help me out with my problem, i cant quite seem to find a solution anywhere. i'm fairly new to excel so please forgive me if i've omitted it somewhere.

More replies
Relevance 70.52%

I have a list of emails in Excel 2010 that are like this:Row L2:[email protected],[email protected] L3:[email protected],[email protected] L4:[email protected] L5:[email protected],[email protected],[email protected] would like to extract all of the emails from a specific domain (@second.com). The text to columns just splits them at the delimiter. Any ideas on how to extract based on the domain? Please and Thank you!

Answer:Excel 2010- extract a specific email from a list of emails

What is the maximum number of email addresses that you might have strung together in a given cell?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 66.42%

Hi there,

may somebdoy please advise (I am pure newbie at Macros):
*I want to create button (I can dot that) and assign Macro to it:
*Copy selected cells (C3:N3)
*Paste values against respective product code (product code can be changed manually in A2)

It happens in the same working sheet (or not a problem if haapens in another sheet)

Many thanks in advance (this would save me some time and efforet)

SM
 

Answer:Solved: Copy selected cells to chosen cells in Excel with Macro

14 more replies
Relevance 66.01%

Hi

I am wondering if there is a simple macro that would allow me to format cells throughout a workbook based on whether they contain an input (ie number/text etc) vs. a calculation (anything with a formula).

Basically I'm looking for an automated way to colour my inputs in blue font and calculations in blank font across all worksheets in a workbook.

Any ideas would be great.

Thanks
 

Answer:Excel macro to format input cells vs calculation cells

I wanted to point out that this can be done without using a macro. In Excel you can click EDIT >> GOTO >> SPECIAL and put a check in the option labeled "Formulas." After clicking OK all your formula cells should be automatically selected on the sheet. Once all cells have been selected you can change the font, background color, etc. for all the selected cells at once. If you prefer to use a macro instead you can try the code I provided below.

Code:
Sub FindFormulas()

For Each vcell In ActiveSheet.UsedRange

If vcell.HasFormula = False Then

vcell.Font.ColorIndex = 5

Else

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Replace with your code to execute if cell contains formula
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End If

Next vcell

End Sub

Regards,
Rollin
 

1 more replies
Relevance 62.73%

Hi All,
I am using LUMIA 1320 that has Lumia Cyan.
Sometimes the time and date is getting reset to a specific date, i.e. 22nd May 2014
Due to this all reminder, alarms and new messages are not coming in sync.
Is this a virus? Does it have come from an app. If that is the case then it questions Microsoft App policies...
Anyone who has encountered this issue and has done something to eradicate it let me know.

Answer:Why is my phones time and date getting reset to a specific date?

First, windows phone can never get a virus. Its the safest platform after iOS . And no, a particular app can't affect the system's time and date. I think your should try to manually set the time and date in the settings. And see if it works.

3 more replies
Relevance 62.32%

System: WinXP and Win98SE

I need help with a script or vbcode that will automatically backup a file or files daily. The current format (so that I can identify the date it was backed up) is: date-filename.bak (ie. 040831-filename.bak).

I would prefer the date be generated automatically, but if there is a dialog box that appears so that the user enters the date manually, that would be fine also.

This particular need is going to be used as an intermediate step to the weekly backups of the datafiles, and is not intended to take the place of the full backup procedure. But I need a step in there incase the the file becomes corrupt, and the user does not know it until several days later, they can go back one or two days, not an entire week, to use as a starting point on the data.

Any help would be greatly appreciated.
Thank you.
 

Answer:Solved: Automated date script

16 more replies
Relevance 61.91%
Question: Automated emails

Hi , I'm unable to recieve automated emails using outlook/microsoft express . I can recieve them through my hotmail account , I have tested the settings for my pop3 account and they are fine . Any ideas ? k_grdn

More replies
Relevance 61.91%
Question: Automated emails

Hi, I'm unable to receive automated emails using outlook/Microsoft express. I can receive them through my hotmail account, I have tested the settings for my pop3 account and they are fine. Also I?m finding myself having to refresh the help room forum page to receive new postings. Any ideas k_g2000.

Answer:Automated emails

We all have to refresh the forum pages to receive new postings - you only download the current page.That's why people often post a message only to be surprised that there are several unexpected postings before them - it happens on occasions whilst typing your posting.

4 more replies
Relevance 61.09%

Hey, so im working on a sheet of around 3 million cells, all of which have some type of formula in them.

Im trying to create a conditional formatting which will change the color of a cell if there is no longer a formula in it.

I found some code that uses the HasFormula Method, returning the value as a bool, and then all cells that return true are then effected by the conditional formatting.

Basically what i need is the code i have right now to just work in reverse, and i can not figure out how to make that happen.

Function HasFormula(rCell As Range) As Boolean
Application.Volatile
HasFormula = rCell.HasFormula
End Function

Any help would be appreciated, thanks.
 

Answer:Highlighting Specific Cells

7 more replies
Relevance 60.68%

Hi all,

I am looking for help on VB script to send automated emails based on the date column in excel sheet..

Uploading the excel sheet as well. The email should trigger on dates in column F to corresponding email addressed in column E ..

The email body should say :
Hi,

Your employee "Column C" is approaching his probation period on "Col D". Kindly confirm if you want to confirm on the date of "Column D" or modify it.
 

Answer:Need help to send automated email based on the date field

Have a look at the attached I have written the code necessary however if you have any changes you would like made let me know.
 

1 more replies
Relevance 60.68%

hey
I need to send many emails from a particular sender to another email..

in GMAIL is it possible to select multiple emails and send them all together??
the filter for sending emails to another email does not work for already received emails

also,
if the above is not possible is it possible to do the same in outlook express 6??
if yes, then how do i download emails from just one particular sender in my gmail account..

if none of the above are possible then what can be don otherwise so that i can forward all the emails i want to at once?

please help
thanks
 

More replies
Relevance 60.68%

Hi there,It's been a while since I've asked for help, but this has given me a headacheI have a number of sheets with various colours in the cells. I want to try this on one sheet onlyAll cells in Row 1 within a Range, that are Green to be flagged with "x"Any ideas?

More replies
Relevance 60.68%

Hi,

I am trying to automate the completion of a monthly cash flow spreadsheet in Excel 2003

I currently create the sheet and link to previous sheet running balances, then manually enter the numbers according to when they come out of the back account.

The data to enter on Bank sheet is listed on Data sheet, and may be any order though I put in date order to make easier to follow when entering manually.

Things to note:
1) It is important that if two payments on same day for same Type then show as "+200+300" rather than just 500

2) If fall on weekend (and thus no date as cloumn header) then must show on next available date.

3) The New Sheet may start on any day of the month.

4) I copy the last week of each workbook to the first week on new workbook, and so the first week tends to be already completed.
If you have any questions let me know.

I appreciate your help.

Cheers
 

More replies
Relevance 60.68%

Hi there,It's been a while since I've asked for help, but this has given me a headacheI have a number of sheets with various colours in the cells. I want to try this on one sheet onlyAll cells in Row 1 within a Range, that are Green to be flagged with "x"Any ideas?

More replies
Relevance 60.68%

Dear everyone, I would like to ask you one question that seems quite easy when seen, but for me, very hard to solve as I haven't found any formula solving it. Please help, if you know how to do it..Here it is. I have a sheet with product numbers (2000 - 3000). Some of them are repeating, some of them are not. I need to write in the column next to it a number like this: If there is one product number, write 1, if there is second one after, write 2 and so on.It should look like this:2001 12002 12003 12003 22003 32004 12005 12005 22005 3I very appreciate your help.With kind regards, Peter

Answer:how to number cells with specific numbers

Assuming your data starts in A1, try this formula. Note the placement of the dollar sign. As you drag the formula down, the top of the range stays constant but bottom will increase, thereby counting each occurrence the referenced value in the range above it.=COUNTIF(A$1:A1,A1)

2 more replies
Relevance 60.27%

Hi,

Can outlook be configured to send automated emails when i am out of office?
If so how is this done?

Rgds,

lee_1133
 

Answer:Automated response emails question.

8 more replies
Relevance 59.86%

Sheets(shtname).Range(Cells(5, colNum + 1), Cells(5, colNum + 2)).MergeHi,Can any one enlighten me as to what I have wrong with the above bit of codeWhat I am trying to do is merge to cells on a specific sheet, 5 rows from the top of the sheet, in relation to a specified column number whose value is generated from another cellso if the colNum = 11, then what I require is to merge cells L5:M5 I am coming up with a runtime errormessage edited by tonygibb

Answer:merge cells subject to specific info

With Sheets(shtname)
.Range(.Cells(5, colNum + 1), .Cells(5, colNum + 2)).Merge
End WithI have my answer guys, this is Ronseal all over

2 more replies
Relevance 59.86%

Thanks in advance for any help you can offer in creating a macro that will accomplish the following:
Select an “open” Deal # from column A in Excel (2002) that meets the following criteria:
1. Has a Begin Date (shown in column F) earlier than today’s date.
2. Has an End Date (shown in column G) later than today’s date.

Each of approximately 6 sheets in the workbook contains about 400 rows of data, columns A-G (data in columns B, C, D and E is irrelevant to this process).

All Deal #s in column A which meet the criteria need to be collected and put in about row 405 with the following typed between each cell name: &" or "&.

The string of cell #s in row 405 ends up looking like this: =A3&" or "&A4&" or "&A5&" or "&A6&" or "&A7&" or "&A8&" or "&A9&" or "&A10&" or "&A11&" or "&A12&" or "&A13&" or "&A15&" or "&A17&" or "&A18&" or "&A19&" or "&A20&" or "&A21&" or "&A22&" or "&A24&" or "&A26&" or "&A27&" or "&A28&" or "&A30&" or "&A31&" or "&A33&" or "&A46&" or "&A47&" or "&A51&... Read more

Answer:Solved: need macro to select specific cells

16 more replies
Relevance 59.45%

i have various blocks of data on one ws, the data is infilled from another ws.what i require is to auto sort that block of data, column '18','9','6','3','2','1', as cell E33 changes,( this is a countif command for this cell). Secondly to this i then need to be able to move to the next block of data, and auto sort that block subject to cell ?33 changing. Information only updates subject to corresponding date entry........please help, very new to the macro/vba side of things and not really sure what i am doing

Answer:auto sort range of cells when one specific cell changes

I'm not sure what you mean by "what i require is to auto sort that block of data, column '18','9','6','3','2','1',"In Excel, columns are usually designated by letters, rows are usually designated by numbers."i then need to be able to move to the next block of data..." What block of data?I think you need to post an example of the data in your workbook and the desired result based on that data. Please click on the following line and read the instructions found via that link before posting your data.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 58.63%

In Excel 2003, I am trying to get the date of a cell in Column R to update when I modify any cells in the row (columns A-Q). Right now I have put this code in the "View Code" option when I right click on the worksheet tab (from this forum):Private Sub Worksheet_Change(ByVal Target As Range)Range("R" & Target.Row) = DateEnd SubI then close the code popup window, save the design worksheet which appears and returned to my spreadsheet, test this and it doesn't work even though the code appears when right clicking the worksheet tab and then choosing view code. What am I doing incorrectly?

Answer:Show date last modified for any cells in row

It's not clear to me what sheet tab you are clicking on but any Worksheet event code has to be placed in the sheet module for the sheet (and every sheet) where you want this to happen.Worksheet event macros are not global, they are Worksheet specific.If you want the date to appear in Sheet1 Column R, then you need to right-click the Sheet1 tab and paste code into the pane that opens.The only other reason that I can think for the code not to work for you (it works for me) is if you have some code that has turned events off.If the line:Application.EnableEvents = Falsehas been run in any macro while the current Excel session is open, then no event code will fire since the execution of that line impacts the application, i.e. Excel.Quitting Excel and restarting it or running the following line will enable events again:Application.EnableEvents = TrueNote: if you have a Macro that runs the "False" line at Excel start-up, then even quitting and restarting Excel won't help since the code will disable events as soon as Excel starts.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 58.63%

Hi,Also, does anyone know how to count the number of cells that contain a particular date in excel, e.g. 31st Dec 2011 across a number of tabs in the same workbook?C.

Answer:Count the number of cells that have a date

Hold down the Shift key while right-clicking the tabs (worksheets) you want to search in (or, if you need to search in tabs that are not adjacent to one another hold, down the Ctrl key instead).Then press Ctrl+F to bring up the search applet. Type in the date (as it appears in the cells), & click on Find All. You'll get the total number of times it appears in all the tabs in the status bar at below left.Here's an example screenshot click here - I searched for something across 5 tabs & it turned out '283 cell(s) found' in the status bar. So the thing I was looking for appears 283 times. G

2 more replies
Relevance 58.63%

I have a worksheet where I need a tally of who is doing what during a certain month.In this case, A is the date, B is the person, C-D-E are the types of tasks.So if A (3/2/17) is in March and B (Joe Blow) worked C (3 Triggers) and D (2 Events) andA (3/15/17) is in March and B (Joe Blow) worked C (4 Triggers) and D (6 Events) and E (1 New)then March's worksheet would show F (Joe Blow) G - total (16) with H - Triggers (7) and I - Events (8) and J - New (1)Does that make sense?

Answer:Only count cells if the date is March?

If I understand your post correctly, you have this:
A B C D E F G H I J
1 Date Name Triggers Events New Name Total Triggers Events New
2 3/2/17 Joe Blow 3 2 Joe Blow
3 3/15/17 Joe Blow 4 6 1Put this in H2 and drag it across to J:=SUMPRODUCT((MONTH($A$2:$A$3)=3)*($B$2:$B$3=$F$2)*(C2:C3))I'm sure that you can figure out the SUM formula for Column G. ;-)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 58.22%

I have a file with a worksheet containing an Excel Table, in which data will be pasted into, say, columns A thru W. Columns X thru Z contain formulae.

I want to be able to protect these formula columns, so they are locked, yet will still expand (autofill) when new data is pasted into columns A - W.

I have successfully done this in one Excel file, but now I come to do it again, I find that the data will not paste in if the worksheet is protected. It looks like it's trying to paste in, as the paste range gets highlighted, but no data is visible.
If I do the same thing with the sheet unprotected, the data pastes in no probs, and the formulae autofills.

I can't for the life of me work out how I did it last time around, and all advice I can find on the internet says it's not possible, yet it clearly is cos I've done it once.

In both instances (the working file and the new non-working file), I first unlocked all cells, then locked cells W1:Z2 (formula column titles and first line of formulae), and then protected the worksheet.

I guess I must have done something else with the original file, but I don't know what....

Any ideas?
 

Answer:Excel 2007 - locking cells in an Excel Table

6 more replies
Relevance 57.81%

hi,i have data like this:8/24/2010 p 152438/24/2010 p 15008/29/2010 n 458/29/2010 p 74108/29/2010 p 6218/29/2010 n 2759/2/2010 p 152439/2/2010 p 1500what I need excel to do is insert a row (blank) after every specific date (for e.g. between the last entry of 8/24 and the first of 8/29).any kind of code that I have written just does not seem to work.. please HELP!!!Thanks..

Answer:insert rows based on date in cells vba

Rather than just giving you the code to do what you want, I think it would be beneficial to you if you posted what you have tried and we'll help you fix it.I'm not asking you to do that to embarrass you or point out shortcomings in your VBA skills, but rather to help you learn from your trials and errors.Sometimes explaining why something doesn't work is more educational that just telling you want does.

5 more replies
Relevance 57.81%

I have a macro whose origin is unknown and it has been working for months with no issues. Today I tried to use it and I began receiving "No cells were found" error. The code is listed below:

Sub CpyBlks()
Dim LR As Long
On Error GoTo Errorcatch
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Range("A13:A" & LR)
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
Errorcatch:
MsgBox Err.Description
'Exit Sub
End Sub

I have tried placing the cursor in cell A13 and also highlighting multiple cell below A13 with no success. The column has a date in cell A13, which could be followed by a blank cell or another date. The contents of the cells below A13 are not consistent.

I am sure that I am missing something very simple, but have not been able to understand what I could be doing wrong.

Thanks in advance
 

Answer:Solved: Fill Date in Blank Cells Below

this line

LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

finds the last row, if you put the date in A13 then it won't go down further because it sees A13 as the last row, if I put the date in A11 it works, but I need to understand what you are trying to do so I can give you an answer.

also the "'Exit Sub" is not in the right place, even if you commenet out temporarily, it should be above the "Errorcatch:" so that it does not trigger the message box unless there is an error.

End With

Exit Sub

Errorcatch:
MsgBox Err.Description

End Sub
when is this macro triggered?
 

2 more replies
Relevance 57.81%

I am trying to get this formula to give me a count of all rows that have a value of "1" in column "AO" and column "AH" "<>Pre-Approval" or ""<>DECL" or "<>WITH" and that have column "AV" blank OR the month number equal to this month (I am using the formula =TODAY() in Goals$S$1) even if the data is filtered out. It works fine except for the month number part, i can't seem to figure out how to tell it to count it if column "AV" is blank or equal to this month.here is the formula I am trying to use:=COUNTIFS($AO$7:$AO$999,"1",$AH$7:$AH$999,"<>Pre-Approval",$G$7:$G$999,"<>DECL",$G$7:$G$999,"<>WITH",$AV$7:$AV$999,MONTH(Goals!$S$1))

More replies
Relevance 57.81%

I am trying to create a planner where one can see by means of conditional formatting if the current time is before or later than a fixed time. For some reason the formala is only seeing the date change but not the time. What is the best way to do this. =IF(C2>$A$2,"OK","nok") both cell [email protected] and [email protected] are formatted date & time

Answer:Using an IF formula in cells formatted as date & time

Your formula should work.How is the Data ( Date & Time) being entered into the cells?MIKEhttp://www.skeptic.com/

2 more replies
Relevance 56.99%

I'm experimenting with dynamic ranges in Excel. I have a workbook with multiple sheets, and there's a repeated column in several of them which always have the same dates. Is there a way that I can update just one of the sheets, and use dynamic ranges in the other sheets so they auto-update? I can't quite figure out how to do it.
 

Answer:Solved: Auto-updating multiple cells with same date

8 more replies
Relevance 56.99%

I need to format my cells in excel to change colour 5 days after date entered.

Answer:I need to format my cells to change colour 5 days after date

With your Date Enterd in cell A1 try:=(A1+5)<=TODAY()MIKEhttp://www.skeptic.com/

2 more replies
Relevance 56.99%

Hi all,

I have 3 columns in my spreadsheet - customer name, expiry date, and days till expiry

What I would like is for an automated email to be sent once an expiry is 90 days away. Within the body of the email it would include customer name.

Please would someone be able to tell me if this is possible.

Thank you for your time!
 

Answer:Automated Email From Excel

8 more replies
Relevance 56.99%

Have seen a few topics on here about this, and it's obviously WAAAAY out of my league.
We have a register of all our operators & drivers and all their various qualifications. I am after a way to set up e-mails or alerts to myself and 3 colleagues whenever a persons expiration of a specific qulaification comes within say, 60 days of todays date. Each person may have up to 20 different expiry dates.
At the minute, I only have a simple conditional format in place showing me Red when expired, Orange within 90 days, and Green when OK. Someone obviously has to physically check this though every day
 

Answer:Automated E-Mails from Excel via VBA

well, it sounds quite similar to the solution I gave this poster...

http://forums.techguy.org/business-applications/940910-emailing-excel-based-off-cell.html

you will need this program to click Yes for you.....
http://www.contextmagic.com/express-clickyes/free-version.htm

If your email security blocks excel from sending
 

1 more replies
Relevance 56.99%

I have a Excel Spreadsheet that currently generates a automated email through outlook. My company recently transitioned from Outlook to Gmail (our email addresses did not change). Is it possible for the spreadsheet to generate a automated Gmail? Thanks for all the help.
 

Answer:Automated Gmail Through Excel?

As per my experience you can send email via Gmail's SMTP servers using CDO - http://www.rondebruin.nl/cdo.htm
 

1 more replies
Relevance 56.58%

Somehow the date on one of my old Excel files has turned from 1999-10-04 (i.e. October 4, 1999) to 4617-04-11. How can I change all the rest of the incorrect dates back to what they should be? This is the only date that I can confirm. And I have no idea what the rest of all the dates should be on this spreadsheet. TIA.

Answer:Excel date problem - to convert incorrect date to original

I have no idea what the rest of all the dates should be on this spreadsheetThen how do you purpose to change them, when you don't know what year they are supposed to be?Somehow the date on one of my old Excel filesAny Macros running that may have in some way impacted the dates?How is the data stored? As Text or as a Date?MIKEhttp://www.skeptic.com/

5 more replies
Relevance 56.58%

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 56.58%

I'm a novice user of Excel 2007 at best, but as an intern at a local company, I've been tasked to figure out a way to have a pre-filled e-mail sent when one of our users presses a radio button. I would also need to send along some specific information from certain cells.

The e-mail would be going to a specific person every time, and I'd like for the employees to only have to choose "Yes" in a radio button for "Send E-mail", then have a confirmation window come up before it is sent out.

Is this possible? Does anyone have any code for a similar function?
 

More replies
Relevance 56.58%

Hi Forum,

I'm having a problem piecing together the various bits of VBA I've been finding on the internet in to something I am able to use on a worksheet laid out as follows:

Column A: Date
Column B: Item Description
Column C: Item #
Column C: Customer
Column D: Customer Email address (each cell contains multiple email addresses)

What I would like to do is have the VBA search Column A for any date greater then 6 months in the past from Today, then forward an email to the address in Column D.
The body of the email would need to contain information from Column B and Column C.

Potential problems:
I need an attachment to the email as well.
Customer ABC may be listed several times (once for each item they purchase) and I would like the email to only be sent once (with all the items) versus many times (once for each item that they purchase).
Would like to bcc the sales team and cc the operations team on the emails that go out. Those email addresses would be static - the "To" part would need to change based on the customer.

I've gone to Ron DeBruin's site but ran in to some problems with the codes that I was trying to change to meet my requirements - so I tried downloading the add in and unfortunately, I am unable to do so from work.

Can anyone help or am I doomed to sorting manually?

Thank you in advance!
 

Answer:Solved: Excel - VBA Automated Email

12 more replies
Relevance 56.58%

I have a project planner (excel 2003) and would like the spreadsheet to email (outlook 2003) me when I have an upcoming due date (preferably the day before).

E.g. Due Date 13.03.2013 would induce a reminder today (12.03.2013)

I won't be viewing the sheet daily and would therefore like this to happen even if the sheet isn't open. Is this possible?

The Due Date is listed in a column.

Are these actions possible? If so, how?

Thanks for your help.
 

More replies
Relevance 56.58%

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 56.58%

Hi

I just started to use MS Excel and I'm having trouble entering data in one specific catalogue.

In one column in that catalogue are digits, and what I have to do is to multiply them all by certain value and then add percentage to them.

Is there a way to do it quicker then one cell at the time, it okay now but as the info building up it will become a genuine pain in the you know what...

Thanks in advance
 

Answer:Excel help - automated process, converting etc.

10 more replies
Relevance 56.58%

Have attached notepad with few data. What exactly i need is listed below:

1. I have 27 folders in my desktop where all the folders contains one notepad as attached.
2. I need a excel macro file which automatically pick the notepad from desktop in that the certain folder.
3. And convert them into excel and save it in the same folder.
For eg: If folder 1 have notepad file with the name "123456", then excel file should save in the same folder where notepad was picked with the name "123456 "B" Ctns "D" Pcs". B- Countif the total "B" and Sum if the total "D"
4. Excel file should do "Text to coloumn" the data in notepad file.
Text to colomn:
*Delimited - Next
*Enable all the Delimiters excep "others" - Next
*Enable "Text"in "Column data format" for first 2 coloumn - Finish.
5.Once everything done in sheet 1.Sheet 1 should take copy of that data and save it in another sheet.
6. Coloumn "A" having a Data of "B" and "D".
7. Add one new coloumn in "A".
8. "B" is the header of "D". So Every header should come left side to "D".
For eg:
IF the DATA is
B STMEGY05066
D 12345678999 8
D 25874136987 9
B STMEGY05067
D 36982117852 10
D 78945612365 2

I need the data like below,

STMEGY05066 12345678999 8
STMEGY05066 25874136987 9
STMEGY05067 36982117852 10
STMEGY05067 78945612365 2

9. Once the above is done. then do pivot th... Read more

Answer:Automated Covertion of Notepad to Excel

16 more replies
Relevance 56.58%

Hi Guys,

I'm new on the forum so wanted to firstly say a quick Hi!!
But I wonder if you can help as I just cant get an issue solved and I'm sure you wizards of the Excel world can help me.
I am trying to get a spreadsheet to send automated email based on a trigger to say If todays date is within 7 days of the due date Then email a list of people in recorded another cell range

I found the following thread which looks really useful but I am getting constant errors when I try to apply the principle to my sheet.
https://forums.techguy.org/threads/...ue-date-reminder-based-on-excel-file.1129238/

I have table which starts in Cell B3.. (Headings in row 2)
Column A is Blank
Column B is Date Raised - this isnt used for anything
Column C is the Topic Of Work
Column D is the Notes from the meeting
Column E is the names of who the topics are assigned to
Column F contains the list of Email address to send that topic to
Column G is the due date (which I want to email out 7 days prior to)
and Column H is where the macro should make that it has sent the emails

The code I have tried is as follows... but I continually get a "Subscript out of range error"
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
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Sheets(OpenActions).Select
lRow = Cells(Rows.Count, 2).End(xlUp).Row
For i = 3 To lRow
toDate... Read more

More replies
Relevance 56.58%

Hi all,

I know this has been posted a few times already, but I am a programming noob and I'm having a bugger of a time altering the code that forum members have already given to others to suit my needs.

What I'm looking for is this: I have a master excel (Office 2013, operating on Windows 7) sheet with numerous tabs (one tab per customer) that has a) their requested dock date for their parts and b) the date I sent them their final invoice, which they need to pay within 30 days. I am trying to get excel to send me an e-mail (to Outlook 2013):

1. 5 days before their requested dock date (so I can make sure our production staff have everything under control and it will be shipping out as scheduled)
2. 5 days before their payment is due (so 25 days from the date I said I invoiced them) and
3. If possible, it would be great to receive an e-mail on the date payment is due as well, as well as in 5 day intervals until payment is made.

Another difference is that I would like all e-mails to go to me, not directly to the customer (so no variable e-mail addresses).

Is this possible at all? I've attached a example of the spreadsheet I'm working with if anyone would be able to help.

Thanks!
Arianna
 

More replies
Relevance 56.58%

Hi Guys,

This is my first post here. I need your help ASAP in developing a mechanism to send an automated outlook mail, 30 days prior to deadline as first reminder and then another reminder at 7 days prior to the deadline. The script should preferably run automatically every time the PC is running. without the excel file necessarily open.

I have searched the forum for similar problems but I am not proficient enough in VBA to modify them to my needs.

In the attached excel file, An email should go to email address (Column D-Some will have more than 1 email entries ), with subject "Task (Column B) is due on Due date(Column C)", and body "Dear Name(Column A), Please complete the task".

Also, the script should put a check mark on Reminder 1 sent column (Column E) (30 days) after the mail is sent, the script should also check if the value of the cell is blank before sending email. The script should put a check mark on Reminder 2 sent column (Column F) (7 days)

I'd really appreciate any help,

Thanks so much!
 

Answer:Automated Email Reminders through Excel

Hi, welcome to the forum.
Have you done a search, there are quite few posts with similar questions and posted solutions, I'm sure your answer is there too.
 

1 more replies
Relevance 56.17%

So I have this excel spreadsheet and an example of it basically looks like this:
Project Phase Person Capacity Start Date End Date
A Design 1 25% 1/15 3/16
A Model 1 25% 3/17 5/11
A Design 2 25% 1/15 3/16
A Model 2 25% 3/17 5/11

I'm looking to track a monthly outlook of capacity % over time for each person (in a pivot table), but I'm unable to get it exactly the way it should be. Essentially I would like it to look like this below:

Dates
Person Jan Feb Mar Apr May
1 25% 25% 25% 25% 25%
2 25% 25% 25%

Is this even possible, either in a pivot table, or even using formulas?
 

Answer:Excel 2007 - Start Date and End Date question

You should have posted that in "Business Applications" here: http://forums.techguy.org/16-business-applications/
 

3 more replies
Relevance 56.17%

Hello,

I have a large report of application forms which have been submitted including the date when they were submitted however the dates are formatted incorrectly.

1. Although the cell formatting is dd/mm/yyyy the data is being shown in mm/dd/yyyy which means the dates are incorrect (e.g. 2nd January would read as 1st February.)
2. As a result of this dates which are passed the 12th of a month are not accepted as dates (it reads the day figure as months and there is no 13th month etc)

I have tried to use the text to columns function but that makes no difference and I have tried to extract just the dates but I've had no luck.

Can anyone help, I've attached a small sample of the data.

Thanks

Daniel
 

Answer:Exported date field not recognized as date in Excel

Where does the data come from?
What do you get if you have no cell format?
 

3 more replies
Relevance 56.17%

I am looking to set up a macro or formula that basically inserts the current time when a cell is filled SO:

when a2:a100 is populated with any thing then the corresponding C cell will be populated with the time.

AND

when e2:e1000 is populated wiith anything then the corresponding D Cell will be populated.

Currently I am using this macro.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A6:A100")) Is Nothing Then
With Target(1, 4)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub

which works great for the A and C cells

but it does not work for the other one.. how do i add and modify this so all my needs are met.
I need another 2 sets of 2 macros accross the sheet for time entry.
 

Answer:Excel date auto populate date/time

11 more replies
Relevance 56.17%

I have a file that lists a Julian date of, for instance, 37573. How do I convert that into something like 11/06/02?

Thanks in advance!
 

Answer:Julian Date--How to convert to calendar date in Excel

That's not a Julian date, Beth.

That is actually a date value that Excel provides.

That's how many days it's been since 1/1/1900.

To change it to a real date, you should be able to select that cell or entire column, hit Format-Cell, Number tab, and choose Date, and then any one of the date formats.

Julian dates are what numbered day of the year. For instance, January 3, 2002 would be 3.

Learn lots about dates and times in Excel:

http://www.cpearson.com/excel/datetime.htm

Be prepared to be there for a while.

Happy Thanksgiving!
 

2 more replies
Relevance 56.17%

So I have this excel spreadsheet and an example of it basically looks like this:

Project Phase Person Capacity Start Date End Date
A Design 1 25% 1/15 3/16
A Model 1 25% 3/17 5/11
A Design 2 25% 1/15 3/16
A Model 2 25% 3/17 5/11

I'm looking to track a monthly outlook of capacity % over time for each person (in a pivot table), but I'm unable to get it exactly the way it should be. Essentially I would like it to look like this below:

Dates
Person Jan Feb Mar Apr May
1 25% 25% 25% 25% 25%
2 25% 25% 25%

(Sorry, the text keeps left aligning so person 2 should actually have 25% under Mar, Apr, and May.)

Is this even possible, either in a pivot table, or even using formulas?
 

Answer:Excel 2007 - Start Date and End date question

16 more replies
Relevance 56.17%

I received a .csv file from a company with a date field that looks like this: Tue Jan 03 12:12:20 PST 2012I have tried using the Data Type options to change this to a mm/dd/yyyy format but it will not apply. I tried converting it to a serial number using*mm/dd/yyyy and it will not work ~ nothing happens. I tried using a DATEVALUE formula but keep getting a #VALUE error .. I obviously don't know how to lay it out. Can anyone give me the DATEVALUE formula for this example? I am not sure what "right", "left" or "mid" represent or how to count the value placements. THANKS!!!

Answer:How do I convert a complex date to a simple date in Excel?!?

Try this:With your date in cell A1,First, format cell B1 as a DATE fieldRight click your mouse, select Format Cell, Select Number Tab,select Date, choose the date format you want.Next, in cell B1 enter the formula:=DATEVALUE(MID(A1,5,3)&" "&MID(A1,9,2)&", "&RIGHT(A1,4))That should give you what your looking for.MIKEhttp://www.skeptic.com/

3 more replies
Relevance 56.17%

Excel 2013 and Windows 10 Pro

Today is May-14

Date format is set to show: Mar-16
Pressing Ctrl+; shows: 05 14 2016 in the cell
and it converts to: May-16 after I exit that cell.

Any ideas as to how to fix this or why it adds 2 days to the date?

I don't do much on computer now because of health but when I do and there is a problem, I know some of the experts are here.

Thank you in advance, Ron
 

Answer:Excel Date shortcut posts proper date and then changes

Hey Ron,

This should be a fairly simple one. I'd hazard a guess and say the May-16 is the month and year rather than the month and day.
If you click the format drop down as shown below, and click the last option for more formatting options.

In the box that pops up, navigate to Custom using the left hand panel to navigate.
Here you can make your own (or use a preset if you wish) format to suit your needs.

Here is a quick key to help you get by:
dd = Day in Number format
ddd = Day in Short text
dddd = Day in Long text
mm = Month in Number format
mmm = Month in Short text
mmmm = Month in Long text
yy = year in 2 digits
yyyy = year in 4 digits

You can separate these with pretty much whatever you want.
All of this data is input into the "Type" field as shown in the picture above.

See the picture for an example, Ironically it is May 16th today so it will appear the same as your issue does, but rest assured, as long as you have those D's in there, it will display the day!

Best of luck,
Let us know how you get on,

Xsage
 

1 more replies
Relevance 56.17%

Hello, I have spreadsheet with a number of dates and times ie (11/19/07 8:57 AM) in column A and data going along with each of these dates and times from columns B-CK. I am trying to limit the list to show the information that is for all times after 2:45PM. The problem is I cannot sort by time and the dates attached to all the times span from 2004-2007. Please help!
 

Answer:Stripping the date portion of date/time in excel

7 more replies
Relevance 56.17%

Hello. I have a cell on my spread sheet which calculates an earliest date due. The cell references other cells to achieve this and looks like this '=C51+(F8*7)' where cell C51 is a date field in the format DD/MM/CCYY and contains the date 26/12/2015. And cell F8 is a number field which contains the value '4'. This value '4' is indicating a number of weeks. So the calculation (F8*7) gets me the number of days and I then add those days to the date in cell C51 to get 23/01/2016.I want this cell to output the current date if the calculation above results in a date that is in the past. Can anyone tell me if there is a simple way to achieve this? Thank you.

Answer:Excel: Substitute a past date with the current date

If I understand you correctly, this should get you what you want:=IF("your calculation"< TODAY(),TODAY(),"your calculation")However, the TODAY() will update every day to always show the current date, assuming "your calculation" results in a date that is in the past.In other words, it will not lock in the first date it calculates. It will always display "today's" date.Is that what you want to see?One other issue: As explained at the link below, the TODAY() function is "volatile". That means that is will recalculate every time the sheet calculates, even if none of the arguments have changed. A large number of volatile functions can slow a worksheet down.https://msdn.microsoft.com/en-us/li...Volatile and Non-Volatile Functions

Excel supports the concept of a volatile function, that is,
one whose value cannot be assumed to be the same from one
moment to the next even if none of its arguments (if it
takes any) has changed. Excel reevaluates cells that contain
volatile functions, together with all dependents, every time
that it recalculates. For this reason, too much reliance on
volatile functions can make recalculation times slow. Use them
sparingly.

The following Excel functions are volatile:

NOW
TODAY
RAND
OFFSET
INDIRECT
INFO (depending on its arguments)
CELL (depending on its arguments)Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 55.76%

Hi the Excel Gurus here,
I have a simple requirement of comparing a huge list of names with the already existing big list of names in another excel sheet. I want the IDs associated with the matching names to be automatically transported to the first excel sheet (containg names to search) and display in adjacent columns.
To make it more clear, say I have Excel sheet A containing names
hoyle,david
singh,naveen
millette,sandra
......

Another Excel sheet contains huge list of names and the corresponding IDs, like
Column A Column B
12345 yang,minh
53478 hoyle,david
76347 khosla,honey

I want to compare all the names in Excel sheet A with the matching names in excel sheet B and want to create 2 columns for employee name and id in Excel sheet A.

Help from anybody to simplify this task would be highly appreciated.

Thanks and Regards,
Everhappy
 

Answer:Automated Excel search and data reorganization

9 more replies
Relevance 55.76%

Hello.
I have been tasked to keep track of follow ups for Implementation dead lines.

This is my first post here. I need your help in developing a mechanism to send an automated outlook mail, when the due date is 14 days away from being implemented/addressed. 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. J), with subject "Audit Follow Up_Audited Function (Col. A) is due on Due date(Col. I)", and body "Dear Name(Col. K), Reminder - Audit Follow up on Implementation Date"
Also, the script should put a check mark on Reminder sent column (Col. L) 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. I've attached my sample spreadsheet.

Thanks
 

Answer:Automated Reminders excel 2007 thru Outlook

10 more replies
Relevance 55.76%

Hi Guys,

This is my first post here. I need your help ASAP in developing a mechanism to send an automated outlook mail, 30 days prior to deadline as first reminder and then another reminder at 7 days prior to the deadline. The script should preferably run automatically every time the PC is running. without the excel file necessarily open.

I have searched the forum for similar problems but I am not proficient enough in VBA to modify them to my needs.

In the attached excel file, An email should go to email address (Column D-Some will have more than 1 email entries ), with subject "Task (Column B) is due on Due date(Column C)", and body "Dear Name(Column A), Please complete the task".

Also, the script should put a check mark on Reminder 1 sent column (Column E) (30 days) after the mail is sent, the script should also check if the value of the cell is blank before sending email. The script should put a check mark on Reminder 2 sent column (Column F) (7 days)

I'd really appreciate any help,

Thanks so much!
 

Answer:Please Help ASAP: Automated Email Reminders through Excel

Hi, welcome to the forum.
Have you done a search, there are quite few posts with similar questions and posted solutions, I'm sure your answer is there too.
 

1 more replies
Relevance 55.76%

Hello Friends,

I have a set of 7 to 8 Dispositions in a cell as a drop down. Every disposition has its own unique notes in the following cell. Assuming A1 has disposition as first in class, A grade, B grade, C grade and failed. B1 contains examination month, C1 contains percentage and d1 contains names of the students. In the last coloumn I want note for every name which should concatenate D1 "has secured" C1, "Percentage in the examination conducted in", B1, and stood first in class, if he is class first. If he failed, it should show me as failed in notes. Can I have a formula.
 

Answer:How to have automated note for multiple despositions in EXCEL

Are you looking to just combine the text from Columns D, C, and B, in that order? Or are you looking to have the cell conditionally show something based on those values?

If you want to combine the values with a space in the middle it would be something like this:

=D1&" "&C1&" "&B1

If you're looking for something else, I'll need a few more specifics. What, exactly, will be in the cells that you're going to base your conditions on? What do you want it to say if those conditions are met? How do you want the notes to be formatted? Those kind of things will help me a little further if the above isn't what you want.
 

9 more replies
Relevance 55.76%

Good Day,

I would like assistance in creating a code that will generate a automated email to specified receipients when a date is entered into a specific cell. I have attached a sample spreadsheet. When a "date" is added in column M, it automatically generates a email to specific recepients notifying the receipients that the specific unit number in column A has been completed.

For Example:

Good Day,

This is to notify you that unit *** (from colum A) is complete and ready for your team.

Thank You,

xxxx

Thank you very much for the assistance!
 

Answer:Solved: Sending a Automated Email Through Excel

8 more replies
Relevance 55.76%

Hello,

I am currently attempting to setup an inventory file in excel to send myself and another colleague anytime an item in that inventory is to expire. I have found a command set from an old comment thread that I have been using as a template but I am not well versed in basic and cannot find where the all of the errors are occurring. So the worksheet utilizes columns a-q, with column Q where the date and time of the sent email will go. I have column A as the item to expire, and column K as the date of expiration.I currently have column p as the location for the email address to be sent but I would rather build the email address into the code since it will only be two email addresses used for the notification. Below is the code that I have been working on but it isn't working for me. Thanks in advance for any and all help.

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

With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With

Sheets(1).Select
lRow = Cells(Rows.Count, 16).End(xlUp).Row

For i = 2 To lRow
toDate = Replace(Cells(i, 11), ".", "/")
If Left(Cells(i, 17), 16) <> "Mail" And toDate - Date <= 7 Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

eSubject = "Test Article " & Cells(i, 1) & " expires " & Cells(i, 11)
eBody =... Read more

Answer:Automated Email for Expired Inventory in Excel

16 more replies
Relevance 55.76%

Hi,
I am a summer student for a concrete company and I am responsible for keeping all of our MSDS up to date. I have a spreadsheet set up to summarize it and that includes expiration dates. Currently the dates change color when it is within 30 days of expiration and to red when it has passed the expiration date. My problem is I have other jobs to do at the same time and don't check the sheet daily. Is there a way I can have Excel email my outlook account when the msds hits 30 days before the expiry?

P.S. I'm running excel 2010
 

More replies