Is there any way to create a specific template that will extract information from PDF file and make it to an Excel File ? The Template would know each PDF and will know which information to extract to excel ?
View 1 Repliesi usually work with 3d and i export all my working in excel to summarise in a table.Now i came across an issue whereby i have a whole exported list for the whole project and i want to extract some of the info to make a new table.My master list is a dynamic one it keeps on updating from the project.
I have attached the sheet, there is one master sheet and 2 other sheets, PID10 & PID2...those sheets are break down table form the master sheet.
I am trying to get a grip on my gas n electric bills. My problem is in creating a formula to convert the info I have into the final bill.
The info I have is -: days of use, amount of G n E used, standing charge, price/unit and discounts applicable. For simplicity I have converted them all into a rate per day and £GB. I tried the following formula
:-(standing charge per day * days used)+(cost per KWH * No.of KWH used) - (discounts * days used) / (100) * (94)
This last one is to account for a direct debit 6% discount. I used cell address for things like No of days usage and KWHs used in the period. The values in these cells have themselves been calculated via a formula from other info. The results I get seem to be a constant variable which indicates that I am doing it wrong.
I used to use Excel many many years ago for all my basic business databases and pivot table reports (so was at a basic competency level) and want to use it again but it has been quite a while and I can't remember how to do the following.....
I want to build a database of sales and purchase info in say 'sheet 1' - let's say 30 different headed columns and then many lines of different information under each column.
In 'sheet 2' I want to create an invoice template of my own design (I can do easily) for printing.
This template will need to extract say 10 bits of information from a particular row and then put them in a certain place on this sheet.
How can I type in one piece of information in a cell on sheet 2 (say the invoice or line number of the database) and that will then trigger all the other needed information from that particular line in sheet one onto this template?
I know it can be done but for life of me can not remember the formulas to use for these other cells in sheet 2 based on the info put in that one 'trigger' cell
I am not sure how would I go about finding the last data entry in a table and using formula to extract information. Attached excel 2007 file to better understand. Need formulas for cell B20 and E20. You can use column A as a reference if it is easier as this column is counting the number of entries and the next value will simply be a +1 to the last value entered in column A(or 75 in our case).
For Cell B20: Need to calculate the days difference between todays date (Cell A20) and the last entry date(in this case B5, this will change as new data will be entered). A subtraction of two dates would suffice - my problem is that I do not know how to find the last entry on the table and use it. You have to remember that table gets updated and new entry will be added so then cell B20 should give the difference between A20 and the last date entered.
i have 2 sheets, one is a database sort. and the other sheet is a template tat i had design. i have inserted a filter to the database sheet that i wan to extract info from my template (diff catergory). but shows nothing,
My user has data in a comment in a cell that provides additional information about the data in the cell. He does not want to have to retype that information into another spreadsheet but wants it to automatically populate.
For example:
Column Headings: A1 = Tardy; B1 = Absent; C1 = Detention
Data in the following cells: A2 = 2; B2 = 1; C2 = 3
Comments are: A2 = Smith - 10 min, Jones - 5 min; B2 = Adams - Sick; C2 = Brown - Talking, Green - Talking, Gray - Sleeping
He wants another workbook that has a column for each name in three separate tables, 1 each for Tardy, Absent, and Detention. He wants the Tardy table to show 10 min under Smith, 5 min under Jones, etc.
Is this at all possible? I will say that I tried to get him to change his spreadsheet so that he inputs the data into the three separate tables then summarizes it in the sheet he presently has but he cannot change the original sheet. It is a shared sheet by many of our office locations and changing it is not an option.
Is there any way.. probably a macro which can extract the following information from a particular folder folder in outlook / PST into an excel sheet :
1. Sender
2. Recepient
3. Received date & time
4. Subject
I am using the vlookup function accross two tabs. . . we'll call them TAB1 and TAB2. On TAB1 I have all my base information which I am referencing from, TAB2 is the sheet where i am using the vlookup formula to extract the information i need from TAB1.
Using cell B2 as an example from TAB2:
I used a vlookup formula in cell B2 and got the information from TAB1. The vlookup formula pulled, correctly, cell F17 from TAB1.
Instead of the formula reading "=vlookup(...)". I would like the cell to simply read =F17.
I have an address list that I need to convert to 3 columns. How do I do this?
View 3 Replies View RelatedIn column A I have various part numbers with alph-numeric characters. In column B I have a similar list. In column C I have the quantities for the part numbers in column B.
What I need is for say a macro or forumla to look at each part in column A and match it with the part in column B and in column D insert the appropriate quantity from column C.
I'm not sure if this falls under say a filter, extraction or search type of function.
My company use a program called Bartender which is a barcode printing program. Bartender has the ability to extract information from an Excel sheet which is really useful and prevents repetetive keying in of information. We have found that if you simply print the Excel sheet when Bartender next uses the file it says it is corrupt and simply copying the data into a new spreadsheet and resaving it as the same name does not resolve this issue.
View 3 Replies View RelatedI need to do an if then statement that takes the date (which is in the form of "3/31/2009" and only use the "3/31" info. Only its slightly more complicated than that, its a formula that links to another program. Basically, the current quarter end date is "curdate()" so if i want a cell to show that, i enter "=curdate()" and i would like to build an if then statement using that date but only using the month/date combo (only possibly dates are 3/31, 6/30, 9/30, 12/31) so I guess I could even use just the month, the problem would be to extract that information in a formula and build the if then statement around it.
So:
if its 3/31, show 1
if its 6/30, show 2
if its 9/30, show 3
if its 12/31, show 4
I am trying to write a formula which will extract information from a given set of cells. The information format is
XXXX-YYYYY-ZZZZ
What I need to do is pull out the information which precedes the second. so in the above the answer would be XXXX-YYYY. I was thinking of using a mid or left, right formula till I discovered that the # of characters for X, Y and Z can be any length ie they could be
X-YYYY-ZZZZ
XXXX-Y-
XX-YYYY-ZZZZZ
So the formula needs to find the second dash (-) and extract information prior to it.
How do I Extract information before a sign "/" in a separate cell ?
View 2 Replies View RelatedAll of the information is provided in one cell, which I can not change. If possible I would like to pull the names from the middle only (example: cell d11 only give me Adam Diaz and so on). Once I have pulled out the name, I would to reverse the name (example: Diaz Adam).
d11=ADIAZ01 Adam Diaz (adam.diaz)
d12=AELIZO02 Alejos Elizondo (alejos.elizondo)
d13=BDELUN00 Bobby Deluna (bobby.deluna)
d14=BFOREM00 Bud Foreman (bud.foreman)
d15=BGREGG00 Billy Gregg (billy.gregg1)
d16=CGREEN06 Christopher Greenwood (christopher.greenwood)
d17=CGREEN08 Cheryl Green (cheryl.l.green)
d18=DDOWNS01 Dickie Downs (dickie.r.downs)
d19=DFLORE03 Daniel Flores (daniel.flores3)
d20=DMENDE01 Donald Mendez (donald.mendez)
d21=DMORAL01 David Morales (david.morales4)
d22=DRODRI07 Domingo Rodriguez (domingo.rodriguez)
d23=DWASHA00 Daniel Washa (daniel.washa)
d24=EBROOK02 Earl Brooks (earl.brooks1)
d25=ECARRE00 Edelmira Carreon (edelmira.carreon)
i have 20 different materials which have codes assigned to them
wh = white
ivory = ivory
db = duck egg blue
etc etc
when i create an order some of the codes will be called up
i want to be able to put them codes plus the description into the worksheet Ideally into the header. if not call a1
If a certain email comes into a mailbox with certain parameters eg subject and from criteria is met. Then extract the data from the email attachment (daily order) and add it to a master sheet containing all orders.
View 1 Replies View RelatedHerewith I attached a sample excel file.
Structure file which i attached herewith include all the cost center details. normally it is a big file contain more than 1000 cost center details. Structure File.xlsx If i want a cost center name for some cost center code, i have to open this file and find a specific name.
What i plan is develop a macro function called "CC".
If I type =CC("D232") I need to print cost center name for D232.
But the problem is i don't want to open the structure file, without open that file, when i type this function i need a result.
I'm having a problem with getting a result from another workbook. The purpose is to extract information from a closed workbook representing data from a country (ie US.xlsm, UK.xlsm) into the open one (summary). I want to count the number of results in a column which are above a certain amount (in this case 100).
So this is my coding for getting this result
=SUMPRODUCT(--(('C:address[US.xlsm]Worksheet name'!$E$2:$E$30000)>100))
Which works no problem for getting the number of results in the E column above 100. my issue is that I want to be able to change the name of the country in a cell, and then get the results automatically from the relevant workbook. I've been using CONCATENATE for these purposes and EVAL (from MOREFUNC)... and I get #REF!
So if I split up the above coding into two cells "=" and "SUMPRODUCT(--(('C:address[US.xlsm]Worksheet name'!$E$2:$E$30000)>100))" (just to keep it simple) and then use CONCATENATE to combine them in a cell. Using EVAL on that cell gives me #REF!
I've tested SUMPRODUCT with EVAL and CONCATENATE on formulae like the one above which do not link to other workbooks, and they seem fine. and SUMPRODUCT and linking to a workbook also works fine in the first example.
So the problem is somehow coming from the combination of SUMPRODUCT, EVAL and accessing an external sheet.
Say I have a string "09800EBHR052708"
How would I take the first 5 characters and get a number out of them?
I am trying to extract unique Data from the information in the output data in the file attached after i have extracted data based on criteria from the database sheet. i have noticed that because i extract data from the database sheet and from there i perform the extraction of the of the unique data , it is not able to extract unique isser name form the output sheet.
View 1 Replies View RelatedExtract data with differing date formats that I need to convert to something consistent that I can format as a date.
This is an example of data.... all with general format at the moment.
2/28/2013 2:48:53 PM
1/16/2013 10:48:50 AM
12/17/2012 11:59:49 AM
I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?
=LEFT(G9,SEARCH("/",G9)+7)
(The day portion of this date always has a leading zero).
Is there any macro that can be used to convert data which have been converted from PDF to Excel. Currently I'm using a traditional way by pressing F2 and enter to convert the data to excel format. It's tedious ( since the data range is quite big) and crammed my finger.
View 9 Replies View RelatedAny way to pull in information from a particular website directly to excel? For example if I have the URL or the specific web page can I then locate specific pieces of information that are contained in specific areas of the webpage and pull it into excel?
View 5 Replies View RelatedWas wondering if it is possible to get below information from Active Directory into an Excel Sheet though VBA. All these details are within an OU in AD: UsernameDescriptionUser Logon NameFirst Name + Last NameTelephone Number I have been searching around and i think I need to connect using ADO, but not sure how to get these objects from AD.
View 1 Replies View RelatedThere are many software solutions for clinical information systems, but they all cost a lot. It occurred to me that a bespoke system could be build using excel.
View 1 Replies View RelatedI was curious if Excel was able to pull information off websites and automatically populate a table such as items from Safeway and a price then another column for Walmart with same item and price? I am betting not on its own but I figured this was the best place to ask since I am trying to find an easier way to find savings at the store.
View 2 Replies View RelatedWhile using Excel 2003, I am aware of "freeze plane" and "split worksheet". The freeze plane comes in handy if I want to the first few rows to remain stationary and displayed while I can scroll down to the rows below. Similarly, the split worksheet comes in useful if I want to have the first few columns remain in place while I can maneuver to the right to see the other columns.
*** THE QUESTION IS: how can I get cells A1 to F20 to remain displayed and unmoved even if I choose to move the active cell around anywhere between G1 to K9999
Are there any good website for a beginner on how to set up and store information in excel as a database? I have mutliple sheets that I use everyweek for payroll and I would like to store the previous weeks data in a seperate sheet so I dont have to save each weeks sheets.
View 9 Replies View Related