Data Lookup And Email Based On Data Extracted.
Mar 27, 2007
I have a excel spreadsheet that has employee names, email address and DOB. The DOB format is 23/03. I need a program that will lookup the DOB row and when it matches the value I enter it extracts the employee name and email address. Once it has this information I need it to copy into an already created email template and send its off. On some days there are more than 1 person with a birthday so it will need to send multiple emails.
View 7 Replies
ADVERTISEMENT
Oct 5, 2008
NOTES BC37Size 3/8 x 3/4 x 3/8 40ft4038Size 3/8 x 7/8 x 3/8 20ft2039 40total041 42Size 3/8 x 1 3/8 x 3/8 20ft 43 Spreadsheet FormulasCellFormulaC37=MID(B37,22,2)C38=MID(B38,22,2)C40=SUM(C37:C38) Excel tables to the web >> Excel Jeanie HTML 4
Problem 1.
With the above I have managed to extract what I need into column C. The formula in C40 does not produce the desired result of 60. C37:C38 is formated as Number.
Column B is populated from a drop down box where the list was populated from a text file converted to Excel.
With that said, Problem 2
If the string that is currently sitting in B42 should end up in in B37 as a choice from the drop down, the formula I have in col C would not produce what I need.
I'm sure what I have in C is not the right approach. And I don't understand why the SUM formula will not work.
View 9 Replies
View Related
May 15, 2008
I have the detail as following in as column A
Employee Name
First line of address (Contain alpha numeric no)
Second line of address (Contain state and Pin code)
Phone no
Fax no
Email ID
Wed site address
First detail field (Contain alpha numeric no)
Second detail field
There is some 4000 Line item in this
Can this line item be arranged in the in Different Column form Column A-I
View 9 Replies
View Related
Mar 7, 2008
I need to pull info to additional tabs. The problem I am having is that the data is sorted daily based upon performance numbers, so the REP names in column A can change based upon the data reported in column C.
What I want to accomplish:
Pull daily data for each unique name in column A to a team tab/worksheet that will be used to report that team's daily data.
Below is a representation of the data I need to break down by Rep Name and Team.
Rep DATA DATA DATA DATA DATA DATA DATA
1 2 3 4 5 6 7 REP 2 27.5% 14
51 86 REP 1 33.3% 14
42 74 REP 3 19.3% 11
57 86 REP 5 19.6% 9
46 66 REP 4 33.3% 9 ...........................
View 9 Replies
View Related
Dec 24, 2007
I have 2 Workbooks. One is invoice data extracted from SAP and the other is a workbook I have to send to the client (has about 10 worksheets containing 5-10 different branches in each worksheet). The layout of the one I send to the client can not change so I am trying to import the values form the 1st workbook (SAP) into the 2nd workbook. What I do now is copy the first 4 columns of data for each branch and paste it into the 2nd workbook.
I was playing with a VLookup formula but I don’t think that is the correct formula. I need the second workbook to be populated with all the invoice info (Number, Type, Date, Balance) that appears in the raw data workbook.
=VLOOKUP(H14,'[abcbank raw data sample.xls]Sheet1'!$A:$A+'[abcbank raw data sample.xls]Sheet1'!$A:$E,4)
H14 is the branch number on the client sheet (I cut the column containing the branch # on the raw data and inserted it before column A).
Raw Data From SAP
View 13 Replies
View Related
Oct 4, 2013
I have two workbooks. One is called the Extraction Sample, which is attached, and the second is the Sorted Results workbook.
The Extraction Sample workbook contains a master sheet with data in a chart, which gets updated periodically. The Sorted Results workbook contains a list of specific extracted results from the Extraction Sample workbook.
The formula I am trying to create for the Sorted Results workbook needs to do two things:
1. Extract only the rows from the master sheet in the Extraction Sample workbook which contain the word 'Thing' in column D.
2. List the extracted rows on the Sorted Results workbook in numerical order using the data from Column B.
View 14 Replies
View Related
Apr 3, 2014
I have VBA code the extracts data from a source and puts it into excel. I need to change the format of some of the extracted data so i can use it for a VLookup formula. Reference the attached example. Columns A AND B have the extraced data which are dates. In Columns C and D or E and F I have typed what I would like the data to look like so I can use in a Vloolup formula. Is there VBA or a formula that word change the format of the dates in Columns A and B to look like C and D or E and F. The changed format could be moved to any new cells or rows.
View 1 Replies
View Related
Aug 24, 2012
I need to split in Excel some thousands of data rows containing text and numbers information of different character length Data to be extracted ( parsed) is stored in a single column( the first one) and needed results look like this:
Data to be extracted
Needed results
Address
Name,Surname
Phone no.
Green Street no 16 Smart town Schmit Anders +3900098787867
[Code] .......
Data is composed by addresses, name of persons and phone numbers. Person's name is has two or three parts, address and phone numbers length is different ( 9 to 12 characters) I need to complete this difficult task (for me, obviously, ) in Excel using a combination of functions and/or multiple operations.
View 9 Replies
View Related
Jun 16, 2014
simplifying a formula which gathers data from about 50 worksheets from within the same work book.
The data to be gathered is in the same cell on each worksheet and is simply a number but i want the SUM of theses numbers carried forward to another worksheet. Each worksheet is named by date i.e. sheet 1 is named "16 June 2014" and sheet 2 is named "23 June 2014" and so on until "30 March 2015" (Each sheet represents one full week Monday - Sunday).
View 5 Replies
View Related
May 7, 2012
I am trying to develop a code which extracts the data from text files inside a folder (Folder test in my desktop) into one sheet. The Macro is in the workbook “Text Extract” which is an excel 2007 file. The data of each text should be copied to Sheet1 of this workbook one below the other. For testing purpose I have kept only one text file in the folder and was trying to copy the data from the text data extracted sheet to Cell A1 of Sheet1 of workbook “Text Extract”. The code works fine till copying the data, but shows below error in the line “Selection.Paste”:
Run time error 438: Object doesn’t support this property or method.
Below is the code:
Sub LoopThroughFiles()
Dim strFile As String
Dim strPath As String
[Code]....
View 6 Replies
View Related
May 23, 2006
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.
Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.
I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?
Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?
attached is book.xls and sales summary.xls for reference.
View 3 Replies
View Related
Dec 8, 2009
How to return all possible values based on a single lookup ( or another condition / macro )
I have a table in B5:E100
In A1 I have a value
I need to look in B5:B100 for the value in A1 then place the contents of B:E for those cells in G5:J5 downwards
View 9 Replies
View Related
Nov 29, 2006
Assuming I have a table such as:
APPLE BEANS CELERY DOUGH
Will 5 10 15 2
Joe 1 3 7 11
Ben 9 6 4 8
What formula can I use to return a result like Celery by looking up Ben and 4 for example.
In other words, how do I lookup the heading of the column based on the the first column and a data point on the same row.
View 4 Replies
View Related
Apr 5, 2014
show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..
see my attached workbook..
View 9 Replies
View Related
Aug 22, 2008
I have a bit of a challenge. You'll have to take a look at the attached files to understand what I am trying to do. I am asking for your advice. I am creating multiple spreadsheets (QPPcompany.xls) that will refer back to a master data(QPPdata.xls) file.
I am trying to figure out how to best set up the data file so that I can do lookups on the data from the referencing files. Here's the challenge. I need to be able to find in the data page the company name in Column A and then how many L's (or C's or P's) they had in a specified month. So for instance I need to be able to find the number 3 in cell E6 and place it in B15 of the referencing file. The required criteria for lookup are Company Name, Month and then L, C or P.
So my question is, is this possible with how my data file is currently set up? I think it may be a problem to have the month/year in row 4 refer to 3 different colums for each month (L, C and P). I don't mind making changes to the data file to make these lookups possible and I think it may be necessary.
View 14 Replies
View Related
Aug 31, 2006
I have an array in excel, 4 columns: Month, Day, Stock, Price. I dont know the most efficient way to handle arrays. I know i can loop through an entire array to find a specific value, but if i have a large array, this doesnt seem efficient.
For example, I would like to find the price in an Array (or Range), when i know the value of two columns. If this were in a database, I would write sql like this: " select Price from TABLE where month = 1 and Day = 3". The result would be 40.21. How can i do this with an array? I have attached a sample spreadsheet with the example mentioned above..
View 3 Replies
View Related
Sep 8, 2006
I have a work book with several work sheets, what I'm trying to do or find a a method of coping data from one sheet to another provided the data in any given cell matches a specific criteria. Example below:
Work sheet 1 is the primary sheet in the work book on this sheet I have a cell with a text value (Bob). Ok on the next work sheet 2 I need to search for a cell that contains (Bob). When or if it is found I need information from other cells in that row (where Bob was found) copied to work sheet 1 in a specified row and colum. If it is not found a negative value can be placed in the specified cells.
View 3 Replies
View Related
Sep 9, 2006
I am setting up a worksheet (1) with a full list of part numbers supplied, cost etc. ie:
Part Description Price Qty
1234 Blah Blah Ł1.00 1
1235 Blah Blah Ł3.00 0
1236 Blah Blah Ł2.00 2
And I have set up a seperate worksheet (2) as an invoice. What I have been trying to do is when I enter a qty in worksheet (1), worksheet (2) will reconise an entry in the qty colum an copy selected fields/ cells. I can do a "=" or use an "if" statement looking at anything greater than "0". but is there a way I can get the invoice worksheet to ignore any lines with "0". So it will only copy over the information with only qtys for the invoice ?.
View 2 Replies
View Related
Nov 19, 2009
I need grouping and then summing in either msquery or back in excel after the data has been extracted from msquery.
I have external data that i have interogated in msquery, and I would like to to sum the amount column if possible.
I have two fields, first field = account number
2nd field = amount due
the first field could contain 5 sets of the same account number, but with different amounts in the Amount Due column. I would like to sum the amounts due for each of the account numbers in one column.
If i cannot do this in msquery, how do i then go about doing it back in excel. I know i can use subtotals, but then when the data updates from the external data source.
View 2 Replies
View Related
Oct 18, 2006
I need to email a page from a worksheet to a series of people and am currently using the following
Worksheets("Report").Activate
ActiveWorkbook.Save
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application. ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
however, I also need the worksheet to be email to the person currently running the macro (i.e. when they email it to the others, a copy is sent to thier own inbox too), is there any way in which this can be done?
The user's email address could be formed from data in the spreadsheet, if there is an easier way to do this (i.e. email address is based on a cell in the spreadsheet).
View 3 Replies
View Related
Apr 23, 2013
i am attaching a sheet here.
i have put data validation list in three columns
from data lsts i select unit type , then hinge type then code.... the problem is that as soon as i select code all the values (H W D ) in the next three cells should automatically change.
the values against each cabinet code are also provided in the same sheet.
Sr No.
Unit Type
Unit Type
Code
H
W
D
Qty
1
Base Unit
Double Hinged Door
B60
720
600
580
2
2
Base Unit
Single Hinged Door
B30R
720
300
580
4
View 2 Replies
View Related
May 6, 2014
The sheet I have attached has three tabs one is a master sheet another is the production input and the last is an engineering input section, I have coded the production input section to input into cells and email the engineers when they have a new job, the issue i have is with the engineering input.
The engineers can currently go into this sheet and input the job number onto the engineering input section and it will show them the current issue from the master sheet (I have already entered 1002 in it), what I want them to be able to do is input into the white sections below the description and when they click on update entry a macro takes the job number entered into B3 and saves the information onto the mastersheet in the correct column/row which corresponds to the job they are updating.
However the engineers may need to go into the same job several times and update different information therefore the fields need to not paste into the cells if the input sections are blank.
Masterfile.xlsm
View 1 Replies
View Related
Jan 8, 2009
I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.
Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.
View 2 Replies
View Related
Feb 4, 2008
I am looking for a way to do what i would call a dual vlookup.
i have some data in a number of sheets and need to do some consolidation into a summary sheet. problem is, not all the sheets have data in the same columns and rows but all the data is the same.
what i am trying to do is something like
vlookup("a",sheet2!A:M,***need to put the column of the data here***,0)
i cant just put in the column number because it changes on each sheet, need some way to do another lookup there, like an hlookup or something, so that i could lookup which column contained 'sales' and return that number so it can be put into the vlookup.
View 9 Replies
View Related
Nov 9, 2013
see sample file, i need a formula to do like B column,
I WANT MAKE LIKE B COLUMN, COMPARE A COLUMN DATA IN C,D COLUMN AND ALL DATA SHOULD BE IN B COLUMN..
View 7 Replies
View Related
May 23, 2014
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
View 2 Replies
View Related
Jul 29, 2012
i have a treatment centre with 22 rooms some with mutiple beds, i need to view occupancy in a grid form to optimise bed useage. i need to look up a date that is after arrival but before depature that matches a room number, i have tried match, index, and, vlookup etc but all this info must exist on the same row
View 1 Replies
View Related
Jul 29, 2008
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
View 9 Replies
View Related
Nov 28, 2006
here is an example....
(this is on a sheet called Summary)
----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649
Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....
---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649
The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).
View 3 Replies
View Related
Jun 12, 2007
I have a very large spreadsheet of customer information(I call it the master spreadsheet). Each row contains only 3 things: Account number, product bought, Price
Later I receive the money from the customer for that product(the pay sheet) that contains the exact same thing in the same order: Account number, Product bought, Price paid.
What I'm trying to do is compare the two spreadsheets so that when i receive the pay sheet of cusomters who have paid with the amount it will deduct it from the master sheet.
So it should compare account numbers when it finds a match then it should subtract the amount paid (column C) from the master spreadheet price column(column C also).
sometimes customers don't pay the right price so it has to be a subtraction so I can see if it was over paied, underpaid etc.
Right now I'm still doing it manually combining the two documents sorting it by account number and checking for matches in column A (account number).
View 5 Replies
View Related
