A1: John Black B1: Jane Black C1: John Black D1: John Black
A2: 24 B2: 32 C2: 23 D2: 12
If I choose "John Black", I would like a list generated that lists 24, 23, and 12 in row 3, in separate columns, but no empty columns between the numbers.
Is there a way than I can take a list of data and create a new list but only listing each value once if there is a duplicate value within that list?
Original List
=========
Apple
Pear
Orange
Orange
Pear
Apple
Peach
Plum
Pear
New List
======
Apple
Pear
Orange
Plum
Peach
When inserting "1" in the cell under soup, i was thinking it will bring up a list of its ingredients and how much of that ingredient. The values are listed in the same spread sheet below.
The amount of the ingredient is in D50 (80grams) and the item type next to it in E50(onion)
Is it possible to put in "1" soup then it displays all the items in both those cells range example(D50-E50 to D60-E60)?
Is it possible to put the value "2" in soup and it shows double the measurements? example (160grams) (onion)
I want to find the first topper student in each subject but the formula is not giving correct output.
View 14 Replies View RelatedIn cell G2 I have a formula that adds the values in column C that are labelled in column B as either 'Apple' or 'Grape'. This is accomplished in the formula using {'Apple', Grape"}. Is it possible to change the formula so that the {'Apple', Grape"} can be replaced with a reference to a range in which the names to be used in the formula will be listed (for example in J1:J3 in the attached file)?
Book6.xlsx‎
I have a column of names. Most of these data show multiple times. Another column has values. I'd like to find the maximum value for each name.
This solution might correspond to "=MAX" the way that "=SUMIF" corresponds to "=SUM".
Note: the table has over sixteen thousand rows; some solutions may work technically but not be practical.
How can I create a macro for excel to delete rows that have the SAME subject? (delete all duplicates - the price etc on that row may differ but as long as the subject is the same, I was to delete it off)
View 4 Replies View RelatedI am using the following code to automatically send an attachment to a specific email with a specific subject line.
ActiveWorkbook.SendMail Recipients:="johndoe@yahoo.com", Subject:="General subject"
ActiveWorkbook.Close SaveChanges:=False
Is there a way to reference a specific field in the active workbook as the subject line?
I've been asked to write a multiple if statement for someone here at work but I've been trying all day with no success. Basically, the OS liabilty in column C cannot exceed £2500. Here is an example inc the expected results............
So in row 1 the expected result is 500, as even though there is a 1000 reserve, 2000 of it has already been paid In row 2 nothing has been paid in column A so the OS liabilty in column C is the reserve up to a max of 2500 In row 3, there is 5000 in reserve, but 100 has been paid so the OS liability in col C must be 2000
In row 4 nothing has been paid in column A so the OS liability is the reserve in column B In row 5 150 has been paid, 150 is in reserve, so 150 is the OS liability
In row 6, 2500 has been paid, but even though there is an amount of 300 in reserve (col B), nothing is expected in OS liability as the 2500 max liability amount has been reached.
I have a spreadsheet where in the past we have used a turnover period (always 12 yrs) as our constant to calculate our average factors. For example we use formulas to calculate up to 12 years and the we just restart at year 13- providing that one of the formulas does not return a 0 value. I need to be able to choose our turnover period- say 8 years and have the values start then but in the next prokect I may need a 10 year turnover.
View 3 Replies View RelatedI would like to select File/Send to/Main Recipient (As Attachment) within my Excel Spreadsheet. Within the email, I would like the subject line to be automatically filled in with an entered value of a cell block from within the spreadsheet.
For Example, If cell block A1 had Thursday entered in the cell. Then once I chose send to/Mail Recipient, The subject line would read Thursday.
Please check this sample file. Say for example; I am sending this file via mail to different agencies. See in this file sheet named base & column title Agency , filters concerned agency wise and send via outlook to the concerned agencies mail id from mailinfo sheet. What my concern is since these are different agencies, I wanted to bring the agency name in the mail subject.
Link below:- [URL] ............
I currently have the below in my VBA which adds the text to the subject of an email:
Application.Text(today - 1, "MM-DD-YY")
today = Int(Now()) is part of the macro. How do i format this to display workdays only.
For instance if today is 07/21 (monday) i need this to display 07/18 vs 07/20.
In excel that function would be =WORKDAY(A1,-1) where A1 is =Today(), just not sure how to translate this to VBA.
I have a simple sheet with a list of costs (small version attacheds as there are 430 items on full list) when i enter "Y" in the move to invoice column I would like the description, multiple and cost details copied to invoice sheet. The added complication for me is that if for example when there is a requirement for multiples of that cost to be added to the invoice.
View 9 Replies View Related cannot seem to get a function that works.
I have a sheet named "Master" with student reference numbers (A2), student names (B2/C2) and their four subjects (D2, E2, F2, G2).
I need a separate sheet for each subject, so potentially 30 additional sheets, and i would like for where a subject is mentioned in one of the four columns of the master, the students reference and names would then be copied and pasted into a row on that subject specific sheet. Leaving the subject sheets with a list of names and numbers of the students studying.
So you would have a student on the master sheet and then they would appear on four additional subject sheets.
Ideally it would also update subject sheets as new students are added. And additional information is being added to the subjects sheets so they cannot be cleared.
I would like to create a list covering multiple columns.
I would like to have 5 columns which will contain the same value/ (word).
If I select,that value from the list, I would like to have each row the word is
found be displayed regardless of which row it is found.
I am trying to send out an e-mail from excel with all kinds of combined info. Here is what I have so far, which works to send out an e-mail to a certain address with a compiled subject. No luck with inserting the BODY of the e-mail yet from a certain cell.
=HYPERLINK(CONCATENATE("mailto:",E32,"?subject=",F32))
Also, How do I make the text in each of these cells read "send e-mail" rather than the whole jumbled mess that is currently in there from the combination of the cells?
editing a datafile. I have a spreadsheet (Sheet2) that looks like this (Start Date is entered in J2 in Sheet 1 and End Date in J3 in Sheet1):
Date & TimePatient IDResultLevel
13/02/2014 14:1818.5>=4 AND <=15
13/02/2014 14:1027.8>=4 AND <=15
13/02/2014 02:231122.5>20
13/02/2014 06:191116>15 AND <=20
13/02/2014 10:081110.2>=4 AND <=15
13/02/2014 14:111113.4>=4 AND <=15
13/02/2014 17:541112.9>=4 AND <=15
[code]....
I need to find the maximum and minimum value for each subject. If the min>4 and max<10 and the subject does not have a value on either the last date (J3 in Sheet 1) or the date before that (J2 in Sheet 1 - 1), I need all of their data removed from the spreadsheet.
I have tried a number of things, but have not been able to figure it out. It may not be available with this particular code, but I am not sure what limitations this code has. Does anyone know how to include the contents of a particular cell in the subject line? Currently the subject line says "Notification." What I am looking for is for the subject to say "Notification - John Doe" where John Doe would be the contents of cell D13.
View 2 Replies View RelatedI have an email hyperlink setup on my excel sheet and I would like to automatically (not manually) add a sentence that is another cell (that changes from time to time) to the subject line or the body of the email.
View 1 Replies View RelatedI use gmail, and have an excel sheet with hundreds of rows. I need to send each recepient an e-mail using their e-mail address (row D), and their name in the body and subject (row A). The rest of the body of the email will all be the same.
View 6 Replies View RelatedI receive around 40 emails per day, all in the following format:
Subject: "Team x.x - date"
Body text: "x files sent"
where x represents a number.
What I'm hoping to be able to do is drop all these emails into a folder (in .msg format), and use excel to look through them and extract the team number from the subject, and the number of files sent from the body text for each message.
Unfortunately, while I class myself as reasonably competent with excel vba, working with emails or .msg files is new to me, so I genuinely haven't a clue how to do this, or if it's even possible.
I have a form which is going to be used as an aid to staff, I have created Macro's that when clicked enter information in to fields in the worksheet, I would also like the macro to generate an email with a specified Subject and body filled out. At the moment all I have achieved is to generate an email through File - Send to - Mail recipient.
I have also tried to have an email created through Outlook using MAPI, but once again have been unable to create an email with or without a subject and message body. Below is how far I have got in creating an email using MAPI.
This is part of Macro to insert information Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("E12").Select
ActiveSheet.Paste
Call SendAMessage
End Sub......................
I have a spreadsheet that lists a set of actions and the days they need to be completed.
The due dates are listed in column c and in column h I have a the user choosing "completed" from a drop down data validation.
I have a dashboard on a separate worksheet, showing if the item is complete and the due date.
Ideally I would like to display the dates on the dashboard in red if they are past due, and the box in column h was not marked complete.
I have been going through my books and trying to solve this, but I run into a different issues.
Even if for now If I can get the main worksheet to display the color conditional formatting that would work
I need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
Is there anyway this can be done using a button in the spreadsheet?
For Example.
I send numerous almost identical forms to a colleague and to eliminate subject line typo's (Reference numbers) i would like the Subject line to match a cell in the document itself.
I have experience in working with Excel, but none on using code/macros
I did this 10 years ago so I admit I am rusty at this but here are what I had in my notes that worked with Excel XP. I am currently using Excel 2010
With ThisWorkbook
.HasRoutingSlip = True
With .RoutingSlip
[Code]....
The last line will send the workbook but I need a Subject and Message to go with it.
When I delete a worksheet, via code, a warning pops up telling the user that a data is subject to deletion.
View 9 Replies View RelatedI need to take contents of cells (which are results of formulas and references to other cells) and use the contents to compose an email.
Here are some cells to illustrate. NOTE: the information in brackets is the formula that's contained in the cell, while the text is the result of the formula.
B3 = This is the link used to compose an email.
C19 = abc@domain.com (an email address)
C22 = Login expired on node citrix08. [=concatenate("Login expired on node ",C7,".")]
C25 = Dear Company A, [=concatenate("Dear ",C6,",")]
C27 = The login for citrix08 has expired. [=concatenate("The login for ",C7," has expired.")
C29 = Contact us within 15 days to continue. [=concatenate("Contact us within ",C9," days ...]
C31 = Ticket 444 has been created to track the issue. [=concatenate("Ticket ",C10," has...]
C33 = Lots of other text
D33 = More text
E33 = More text
C48 = Sincerely,
B3 (Named "SEND MAIL") contains the formula used to compose the email from the cells above, however, I cannot add all the cells to the formula without an error. At some point, there is too much data (contained in the referenced cells) and B3 returns #VALUE error.
=HYPERLINK("mailto:" & C19 &"?subject=" & C22 & "&body=" & C25 & "%0D%0A" & "%0D%0A" & C27 & "%0D%0A" & "%0D%0A" & "%0D%0A", "SEND MAIL")
The above formula works now, because it doesn't contain all of the body (of email) I need.
When I click on B3, it properly launches the email client, puts in an address, subject, and two lines of text in the body.
My workaround to this problem is to manually highlight cells C29 through E48, select copy, then go to the partially formed email and paste the rest of the body.
Is there a way to include all the body into an email? I think this can be accomplished with a VBA code, but have no idea how to do it.
I dont want to send the mail, just compose it using cells that have calculated what is needed in the body. I can then read the email, confirm it's content, and click send in the email client manually.
Does anyone have any suggestions? Is there a better (but simple) approach?
I know this could be done via javascript on an html page, but I dont know how to code this at all.
I do know excell quite well and have many cells to calculate what is needed in the body of the email, but, alas,
still can't compose the ENTIRE email from the formula in B3.
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
