Over 1 million tech questions and answers.

Excel - Auto Email based on cell value

Q: Excel - Auto Email based on cell value

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.

RELEVANCY SCORE 200
Preferred Solution: Excel - Auto Email based on cell value

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.)

A: Excel - Auto Email based on cell value

Read other 10 answers
RELEVANCY SCORE 98.4

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.
 

A: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

Read other 1 answers
RELEVANCY SCORE 95.2

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

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

anybody ???
 

Read other 2 answers
RELEVANCY SCORE 95.2

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!
 

A:Automatic Email from Excel based on Date in Cell

Read other 16 answers
RELEVANCY SCORE 95.2

Hello,

I have seen posts similar to my requests but have been unable to put together a code to do everything I need it to do as I am very new to VBA coding.

I am looking for a VBA code that will generate an email when the date in a cell of a column is 7 days or less from the current date. The email will be sent to a recipient listed in another cell column and cc my coworker. I also need a code to change the data in another cell to "Approve, Email Sent" when the same cell date is between 7 and 1 days from the current date and "Expired, Email Sent" when the date equals the current date so that an email will only send if this cell does not equal "Approved, Email Sent" or "Expired, Email Sent" so that emails are not repeatedly sent every time the spreadsheet is opened. I plan on running task scheduler to open the spreadsheet every Monday so the code will run weekly at minimum.

Any help is greatly appreciated, thank you!
 

Read other answers
RELEVANCY SCORE 94

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!
 

A: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.
 

Read other 1 answers
RELEVANCY SCORE 94

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!!
 

A: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
 

Read other 2 answers
RELEVANCY SCORE 93.2

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
 

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

Read other 16 answers
RELEVANCY SCORE 93.2

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.
 

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

Read other 6 answers
RELEVANCY SCORE 93.2

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

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

Read other 8 answers
RELEVANCY SCORE 90

Hi guys,

Really need your assistance ..
Attached is example for my problem.

I only know how to set the parameter for the 'status' (using condiitonal formatting), but i dont know how to create the code in VBA to create emails on certain condition .. I'm not used to VBA, and still learn about it (my head was spinning lately).

So, I wish someone here could help me ..

Regards.
 

A:Solved: auto email based on certain condition in excel

Read other 16 answers
RELEVANCY SCORE 90

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!
 

A: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.
 

Read other 2 answers
RELEVANCY SCORE 86

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.

Read other answers
RELEVANCY SCORE 86

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.

Read other answers
RELEVANCY SCORE 86

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
 

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

Read other 12 answers
RELEVANCY SCORE 77.2

Sounds like a simple thing but I’m 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 I’d 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
 

A: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
 

Read other 2 answers
RELEVANCY SCORE 77.2

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.
 

A: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
 

Read other 2 answers
RELEVANCY SCORE 77.2

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)
 

A:Solved: Emailing from Excel Based off Cell Value

Read other 16 answers
RELEVANCY SCORE 76

In order for my wife to get reimbursed, my wife has to turn in a Mileage Log every 2 weeks. In her Mileage Log form, each cell in the "Starting Location" (Column B) and each cell in the "Destination" (Column C) has a list of addresses from which she can select. The lists are generated from the DATA Sheet mileage chart

As an example and in other words, if we select the address at 417 Victoria Dr., Kerrville in Column B and select the address at 110 Ash, Ingram in Column C. By looking at the mileage chart on the DATA Sheet, we see the distance between those 2 places is 10.6 miles. Presently, we have to enter that mileage manually in Column F.

Is there a way to have excel get the mileage number from the DATA sheet based on the locations selected on the Mileage Log sheet. I could probably use a lot of nested IF statements but that would be real cumbersome and very hard to modify as she has clients come and go from time to time in which case I would have modify each IF statement. I'm sure there is a better and simpler way.

Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 8.1, 64 bit
Processor: Intel(R) Core(TM) i7-4790S CPU @ 3.20GHz, Intel64 Family 6 Model 60 Stepping 3
Processor Count: 8
RAM: 12227 Mb
Graphics Card: NVIDIA GeForce GTX 750, 1024 Mb
Hard Drives: C: 1848 GB (1769 GB Free); D: 2794 GB (2536 GB Free);
Motherboard: ASUSTeK COMPUTER INC., K30AD_M31AD_M51AD_M32AD
Antivirus: Webroot SecureAnywhere, Enabled and... Read more

Read other answers
RELEVANCY SCORE 76

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?
 

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

Read other 6 answers
RELEVANCY SCORE 76

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
 

A:Emailing multiple recipients from Excel Based off Cell Value

Read other 9 answers
RELEVANCY SCORE 76

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
 

A: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.
 

Read other 1 answers
RELEVANCY SCORE 76

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
 

A:Excel Generate emails based on conditions of cell.

Read other 10 answers
RELEVANCY SCORE 76

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
 

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

Read other 9 answers
RELEVANCY SCORE 76

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
 

A: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
 

Read other 1 answers
RELEVANCY SCORE 75.2

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
 

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

Read other 7 answers
RELEVANCY SCORE 75.2

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,
 

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

Read other 10 answers
RELEVANCY SCORE 75.2

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.
 

Read other answers
RELEVANCY SCORE 75.2

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?
 

A: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.
 

Read other 3 answers
RELEVANCY SCORE 75.2

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
 

A: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.
 

Read other 3 answers
RELEVANCY SCORE 75.2

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
 

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

Soooo... is this possible or no?
 

Read other 3 answers
RELEVANCY SCORE 74.4

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

 

A: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
 

Read other 1 answers
RELEVANCY SCORE 74

I have built a simple timesheet for someone in my dept, which includes amongst other things:

- hours worked over a month,
- how many hours owed/extra worked
- a cumulative of this figure over the months.

The requestor wants to be able to type a letter such as 'e' to represent a 07:30hr shift. So they type 'e' and the cell contents change to 07:30. There are about 6 different codes.

Normally a Vlookup would work EXCEPT you can't change the contents of the cell your are working upon.....cicrular referencing!

I could see two ways forward: either a function that allows the above

OR (perhaps more favourably)

leaving the lookups until the hour calculations at the base of the form. i.e Instead of a simple Sum for the hours worked that month, it could read the values in the range, look them up from a table(vlookup), and then sum the looked up figures to give the answer in the 'hours worked' cell.

I vaguely remember this being possible from my university days but can't remember how.

Can anyone help me?
 

A:Excel 2003: automatically changing a cell's contents based on keyword entered

Read other 9 answers
RELEVANCY SCORE 73.2

Hi,

My VBA/Macro knowledge is very basic, but I am able to record macros and edit them. I have a scatter plot (with 5 series) and I want to automatically change the line style (i.e. solid or dashed etc.) depending on the text in a cell. This is what I tried, but I cannot get the code to reference the cell which contains the text:

--------------------------------------------------------------

Sub Macro14()
'
' Macro14 Macro
'
'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
End With
End Sub
--------------------------------------------------------------

For the line ".DashStyle = msoLineSolid" I tried ".DashStyle = Range("A7").Select" but this does not work.

Is there a simpler way to set-up a macro to format graphs automatically based on a cell content/format/colour, does anyone know a good guide to do this?

Thanks
 

A:Excel Macro to automatically change chart line style based on cell text

Hi,

I'm not familiar with charts. But, see if this helps.

http://social.msdn.microsoft.com/Forums/sk/isvvba/thread/50180062-37e0-435e-bf52-0f6de482d8ef
 

Read other 3 answers
RELEVANCY SCORE 71.2

Dear Friends,

I'm in a serious need of updating the respective teams with reminders on due dates of statutory compliance requirements. I've developed an excel workbook having three sheets namely Permissions, Returns and Records. All the three sheets are having due dates. (Workbook is attached for your solution please)

I need your expert solution in sending an e- mail message as a reminder to the person concerned before 15 days of due date with an optional text of my choice.

Its not possible for me to open neither the excel sheet nor the ms outlook application on a time basis coz my profile mostly deals with Industrial Relations aspects. Hence I've to be in the field 12/7 during the shift period and on call 24/7.

Awaiting early reverts.

Regards,

SNU.
 

Read other answers
RELEVANCY SCORE 71.2

Hi,

Got a few questions which tie in to what i am doing so i'll try to explain as best i can.

I want to be able to have excel automatically arrange the order in which it displays the information based on the value or information in a cell. Eg i have a list of things i sell online and I track their performance using the spreadsheet were i have a sheet for every product type which are linked to a master snapshop sheet which has the prices, weight and other info about the items. The product name on this main sheet is hyperlinked to the sheet containing the product and in turn the data in that sheet is formulated back to the main snapshot the aim being as little data input as possible to calculate everything.
I would like to be able to have the snapshop mainsheet automatically put for example the best performing product at the top of the list. At the moment a have to select the cell and expand the data range but this is erratic as i don't think i do it properly. Sometimes it messes all the data up. I am not that good with excel so i am probably going to be told why don't you do this or that etc but that is ok if anyone has an idea.
Anyone able to help?
Apologies if its difficult to follow but i will give more info if requested.
If someone has a link to a webpage that can help would be great also.

Regards,
lee_1133
 

A:How do you Auto arrange cells in excel based on their values?

Read other 16 answers
RELEVANCY SCORE 70.8

Hi, I hope this is a fairly simple question. I think I have done it before but I cannot for the life of me remember how, nor can I find anything that explains it.

Anyway, what I'm trying to do is use the auto-fill handle to drop down a very simple formula but I want one of the cell ref numbers in the formula to remain the same while the other changes.

EG. I want the first formula to be A1*B1 and the second one to be A1*B2 and so on A1*Bn using the auto fill command.

Hopefully all the wizards on here understand what I am trying to explain and can show me how this is done.

Cheers

Ryan Newton
 

A:Excel - Holding Cell Refs during Auto-Fill

Create an absolute cell reference by using $ signs like this: $A$1+B1
Now the B column cell reference will increment while A column will remain constant.
 

Read other 3 answers
RELEVANCY SCORE 70.8

I'm having an issue with an excel formula.

I have:
Code:
=
IF(D7="DSOA",'Common Milages'!D8,
IF(D7="SRHS", 'Common Milages'!D6,
IF(D7="ECT", 'Common Milages'!D7,
IF(D7="-","0",
))))
This code works fine on a single cell. The problem occurs when I try to duplicate it, either by using 'fill series' or copy and paste.

Excel automatically increments the cell numbers, so that D7 is D8, and so on as it goes down. This is a good thing.

But the formula also references a second sheet 'Common Milages'. The cell values in the formula that references this sheet always needs to stay the same. In other words 'Common Milages'!D6 should always be 'Common Milages'!D6, no matter where the formula is located.

Is there a way to exclude certain cell numbers from auto-incrementing?
 

A:Excel Formulas: Cell Value Auto-Incrementing Issue

Read other 6 answers
RELEVANCY SCORE 70.4

Hi all, I am trying to move data in excel and am hoping you can help.

I have an equipment inspection system with a seperate worksheet for each item of equipment and a record of each monthly inspection result to build up a historical record for each item.
What I would like to do is have a final summary sheet which shows the current status of each item (Date of insp., who did it and result - pass or fail). I am wondering if there is a way of automatically updating the summary sheet with the latest results.

Any assistance would be appreciated

Roy
 

A:Solved: Excel - Cell content movement and auto update

Read other 7 answers
RELEVANCY SCORE 69.6

I am new in VBA world and i have a question about connecting MS outlook to MS access (2007 or 2010). let me explain what i want exactly by telling you what kind of project i wanna do.

I have an access database with a lot of data and tables, when i receive any email with certain subject like if i receive an email says in the subject line "Emp 1232221" then outlook when see any email with subject start with "Emp" a VBA code should go to MS access table called "employees" and get all the data belong to employee no "1232221" put them in excel sheet and reply back with this sheet as an attachment.

is that doable??
 

Read other answers
RELEVANCY SCORE 68.4

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
 

A: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
 

Read other 1 answers
RELEVANCY SCORE 68.4

Hi guys,

Thanks for sorting me out last time, however I have another query:

I am using this VB script to email values in cells, the problem is when people have autosignatures with no carriage returns the data in excel is put on the same line as the auto sig, is there a way I can get a carriage return after the data output? I have tried adding Chr(13) (in bold below) but this didnt work.

Any ideas?

Thanks,

Sean

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String

Email = " & "," &

' Message subject
Subj = ""
' Compose the message
thisrow = ActiveCell.Row
Msg = ""
Msg = Msg & Cells(thisrow, 3) & Chr(44) & Space(1) & Cells(thisrow, 4) & Chr(44) & Space(1) & Cells(thisrow, 5) & Chr(44) & Space(1) & Cells(thisrow, 6) & Chr(44) & Space(2) & Cells(thisrow, 7) & Chr(44) & Space(1) & Cells(thisrow, 8) & Chr(44) & Space(1) & Cells(thisrow, 9) & Chr(44) & Space(1) & Cells(thisrow, 10) & Chr(44) & Space(1) & Chr(13)

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20&qu... Read more

A:Excel Cell Email update

Read other 9 answers
RELEVANCY SCORE 68.4

Hello,

First time post from me! So hi everyone.

Im a begginner at this so any help would be appreciated.

I have created a training matrix on Excel, and it obviously peoples training runs out regularly. Somehow i need to try and get an email sent to 4 different email addresses when somebodies training is a month before running out. Then, if its still not updated, another email to be sent out 2 weeks before the 'date expiry'.

I've tried messing about with Task schedular and have got an email to be sent out every Monday morning at 9am, to various email addresses. However, ideally i need something which would send an email alert as i explained. Ive tried with macros and visual basic codes, but its just a bit too much for me! haha

I understand there have been similar posts, and i have tried to adapt to them, but still doesnt seem to work out.

Any help whatsoever would be fantastic.

Look forward to your reply.

Thanks
OW
 

A:How to send an email from excel when a cell changes.

Read other 16 answers
RELEVANCY SCORE 67.6

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
 

A:Set email reminder based on conditions on Excel

Read other 10 answers
RELEVANCY SCORE 67.6

I want to have an email sent with the topic of the training and the name of the person. this is according to the date but since it has a lot of trainings and dates it works with columns instead of rows.

It has two due dates one for a yearly email reminder and one for every 3 years.
 

Read other answers
RELEVANCY SCORE 67.6

Hello again,
Im trying to create a macro that sends an email that displays a Message. I have created the button that i assigned the macro to. I have gotten to the point of writing the message, but i cant get what i want. What I want is for the body of my email to contain my text and the values of cell A1. Right now, all i can get is it just to display my text. I cant get the string to add values from the worksheet. Please Help

Code:

Sub Mail_small_Text_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String


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


strbody = "Dear customer,"

On Error Resume Next
With OutMail
.To = "[EMAIL="[email protected]"][email protected][/EMAIL]"
.CC = ""
.BCC = ""
.Subject = "New item set up completed"
.body = strbody

.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub



 

A:Email in excel to display cell value and text

Hi, if the text is the same for all message you can add that as default value foor strbody
e.g:
strbody = "Dear customer," & vbcrlf & "This is the text that will go into the message body" & vbcrlf & vbcrlf
.Body = strbody & sheets(<your sheet name>).range( "A1").value

This is one way to do it, if you want other methods, just 'holler'
 

Read other 1 answers