Excel 2003 :: Extract Information From Spreadsheet Database Into Another Sheet With One Trigger?
Nov 27, 2012
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
View 4 Replies
ADVERTISEMENT
Oct 23, 2013
(EXCEL 2003)I have 6 worksheets for 6 separate ad reps that will be populated with customer info. To make my life simple I just want column A from each of the 6 worksheets to show up on a master sheet in column A. But what also needs to happen is if we add and delete customers that also needs to happen on the master. I understand the "=" and then pointing to a cell in another sheet and hitting enter. I got that to work but I just have soooo many cells to work with that would just be way too time consuming.
View 2 Replies
View Related
Jul 29, 2013
i 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.
View 12 Replies
View Related
Oct 14, 2008
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
Jul 1, 2014
I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......
I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....
Currently what i am trying to do is to get one of the minor files to auto populate an area of the master database. I will try to explain it below...
1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').
2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..
3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed
4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.
5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)
6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....
To make mattters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red
View 14 Replies
View Related
Jul 27, 2014
I have a number of separate worksheets in one spreadsheet all based on the same list (eg customers); the customer's name is the first column and hence the 'key' in each worksheet; when I insert or delete a line in the main worksheet the formulae in the first (customer name) column are amended in the other worksheets BUT I ideally need more than that; when I insert a new customer in the first (main worksheet) I need a new line with that customer name inserting into the corresponding place in the other worksheets; and when I move a line (eg delete a customer and move them to the bottom of the main worksheet list) I need the corresponding lines in the other worksheets moving as well. I guess what I really need is a drill-down function; a main customer list and sub-lists all linking back to the main lists like you would get in database tables.
View 5 Replies
View Related
Dec 9, 2011
I'm tasked with looking up part numbers in Epicor's ERP database and entering the labor cost into excel. Is there any way to write a macro or VB program that lokks at the part number in excel, goes into epicor, pulls finds the part number and cut and pastes the cost data.
I have about 30,000 of these to look up.
View 1 Replies
View Related
Jan 29, 2012
While 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
View 9 Replies
View Related
May 23, 2012
Macro to find data from a huge database of items of equipment and find certain ones relevant to an area. I am using Excel 2003.
I have a spreadsheet which has three tabs.
Tab one has a list of equipment with a Ref (Col A), ID (Col B), mile (Col C) and chain (Col D) start and a mile (Col E) and chain (Col F) finish.
There are about 25 different Refs and IDs, but all rows have different Mile and Chains.
The second tab is an identical template of the third tab which is where I want the results to go (see below)
The third tab is raw data which list thousands of items but I want the macro to find the items which are in the first tab using the ref, ID, mile and chain information.
View 3 Replies
View Related
Apr 16, 2012
What I am trying to do is extract all the data from a excel database based on criteria. When i specify the site and month I need to pull all the corresponding data and write it to a worksheet. Below is an example. I am not allowed to show all the data it is confidential.
Site Date Operator Month
Chicago 12/3/11 Daniel December
San Jose 4/8/12 Mike April
New York 4/8/12 John April
View 1 Replies
View Related
Feb 9, 2014
create an excel spreadsheet, where i can enter all my products name, unit price etc. so when i am doing an invoice for the customer, if i just type the product name price will show up automatically.
View 4 Replies
View Related
Jan 21, 2009
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 Related
May 21, 2014
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 Replies
View Related
Jul 4, 2014
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 Related
Nov 21, 2012
I have a text box in a user form that when the information is added and the command button is clicked I would like that information to go to this workbook, which is in a different location.
H:Burney TableMaterial That Needs AddedMaterial to be added.xls
I need the in formation to stay in Column A starting in Row 2.
The next time info is entered into the text box and the command button is clicked i need the information to go to the next empty row in the workbook
View 3 Replies
View Related
Apr 7, 2014
I am new to Excel VBA and am trying to create a macro that will draw information from a spreadsheet. The code i have written so far is shown below:
Sub Event1()
'Dim Event Date As Double
Dim Message As String
Dim Ans As String
' Prompt for Data
[Code] ......
The section i am having issues with is shown in red above.
What i am trying to do is have a message box pop up with the information as shown based on a date being typed into the the original box that pops up (Please enter a date and click on OK). The following message box then displays the required information from the spreadsheet.
View 4 Replies
View Related
Apr 18, 2012
In Excel 2003 I am trying to compare two lists of names on the same spreadsheet to determine which names are different between the two columns of names. What is the easiest way to do that?
View 1 Replies
View Related
Feb 27, 2014
When I enter a value into a cell and press "ENTER" or press the Right Arrow the whole spread shifts to the left instead of just confirming the entry of the cell and moving one cell(column) to the right.
This is a spreadsheet that I have used for over 3 years so something has changed in one of the settings.
I have Office 2003.
View 2 Replies
View Related
Dec 17, 2011
[URL]
My Excel Spreadsheet has columns named "Hospital Name | Overview | Address | Phone | Fax | Contact Person 1 | Contact Person 1 Title | Contact Person 2 | Contact Person 2 Title | Contact Person 3 | Contact Person 3 Title"
There is the list of hospitals starting from "Abbott Northwestern Hospital". I have two things in my mind to extract the information. If you click on the first hospital name "Abbott Northwestern Hospital". Below is an example text which I would like to transfer it automatically in columns given above;
This text should go in column named "Overview":
Abbott Northwestern Hospital bring twins into the Twin Cities -- along with triplets, quadruplets............
This text should go in column named "Address": 800 E. 28th St.Minneapolis, MN 55407
This text should go in column named "Phone": 612-863-4000
This text should go in column named "Fax": 612-863-5667
This text should go in column named "Contact Person 1": Jeffrey D. (Jeff) Peterson
This text should go in column named "Contact Person 1 Title": President
This text should go in column named "Contact Person 2": Daryl Schroeder
This text should go in column named "Contact Person 2 Title": VP Operations
This text should go in column named "Contact Person 3": Sandy Schmitt
This text should go in column named "Contact Person 3 Title": VP Strategic Development, Allina Hospitals and Clinics
View 9 Replies
View Related
Jul 5, 2014
I am setting up a template to use to track candidates throughout their lifecycle. I am also creating a 'summary report' on another worksheet that counts total number of candidates, counts the number of candidates that have been submitted to the client, count of the number of candidates that have been interviewed by the client, etc.
How do I count the number of candidates in the spreadsheet?
In Cells A6:B5, I have "Submitted"
In Cells A7-?? I have dates for those candidates that have been submitted.
Then a new section titled, "In Process"
A list of candidates are contained within that section.
Then a new section titled, "For Review"
etc.
There are multiple sections, so the date in Column A1 may not always be in a constant row, as it depends on how many candidates are within a certain 'phase'.
I have a column I5, titled "Status" This column has a built in drop-down list that contains pre-defined status, for example, "Submitted", "Candidate Declined", "2nd Message Sent", etc. I need to be able to create a summary report based on a count of candidates that fit certain 'Statuses'. For example, count the number of candidates that were Interviewed. This could be a combination of Statuses: Phone screen with client, 1st F2F, 2nd F2F, etc.
(At home, I am using Excel 2003. At work, I am using Excel 2007. So if it's easier to create in Excel 2007, that's fine.)
View 4 Replies
View Related
Dec 31, 2008
I have a bit of code that calls a formatting sub depending on which cell is modified. It is triggered by the Worksheet_Change event, determines which cell is modified, and either calls the formatting sub or doesn't based on the location of the modified cell.
Some of the columns in the sheet have data validation with drop downs. If I select a value from the drop down, it doesn't trigger the Worksheet_Change. If I type a value into the same cell, it does.
This was apparently an issue in Excel '97, but supposedly fixed in '03?
View 9 Replies
View Related
Nov 29, 2012
The attached is a race keeper's scorecard.
Tab 1 lists all participants and race times.
Tab 2 generates a printout to post on a wall.
The spreadsheet works great, BUT, I forgot I needed to add a condition of DNS (Did not start) and DNF (Did not finish).
Once this is added, of course, it breaks everything done so far (damage being done in column M on tab 1 and all of tab 2).
My ideal state is that a DNS or DNF can appear in column L on Tab 1. . . and these participants fall to the bottom of the list generated on Tab 2.
I know there are miracle workers out there who can make this happen! Again, hoping for no code, and no manual manipulation if possible (end user is not Excel savvy). Must be compatible with Excel 97-2003.
View 10 Replies
View Related
Mar 28, 2014
I have a folder with multiples excel sheets
Destination : C: Project CustomerExcel
I would like to extract and compile the information contained in these cells:
a5,c5,a6,c6,c7,a14,g14,e16,g16,e18,i18,a20,g20,h22,j22,h24,l24 all the sheets.
New sheet would contain the information of each sheet eg. Column A2= file name
And Row B2 to R2 or whatever will be the corresponding cells mentioned above for each cell. The code I have only brings back the file name but only a5 from the range but not the rest.
VB:
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
[Code] .....
View 5 Replies
View Related
Dec 28, 2013
Is there a way without using code to have the text in a text box (excel 2003), copied to another cell or another text box on a different worksheet?
I have information in a text box on 1 worksheet. I would like this information to automatically be copied to another worksheet. On the master sheet, if any of the information gets changed or updated, the copied information should get updated as well.
View 1 Replies
View Related
Mar 25, 2014
I have two Sheets, first is Service Data, the second is Log Sheet. In the Service Data sheet I have a column of serial numbers in column A. I also have a column of serial numbers in the Log Sheet, column A.
I'm looking for a conditional format whereas I enter numbers into column A on the Log sheet it will auto highlight the cell if I duplicate a serial number from row A in the Service Data sheet. I would like it to highlight the Duplicated number on the Log sheet. I know new versions of excel make it easier to do this but unfortunately I have Excel 2003.
View 3 Replies
View Related
Feb 20, 2014
I work for one half of a joint venture & am responsible for planning & expediting. The other half does purchasing. The bi-weekly PO download reports I receive are less than useful. I have already written the code to delete undesired sheets & add, format, and enter headers for a "Summary" sheet.
I need code to move to the next row, and run formulas to pull data from the next sheets, and repeat until there are no more sheets.
The number of sheets will vary from one download to the next, and the sheet names will vary from one download to the next.
Following are example formulas that need to be run on successive rows while pulling from successive sheets.
I am running Excel 2013 on Windows 8.1
View 14 Replies
View Related
Mar 15, 2013
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
View 9 Replies
View Related
Dec 17, 2009
I just wanted to know if there was a way to add the information contained within an excel form into an access database using a macro? What I am looking to do is automatically have the data added when I press a button, or when I close a worksheet.
View 14 Replies
View Related
Feb 22, 2013
I'm trying to find the coding to be able to do the following.
If Manufacture = Dimond in Cell C3 then in C5 show list from database Worksheet, of A4-A9, if C3= Steel & Tube then show list for A13-A18
Cell C3 is a Data Validation List
I keep getting name errors etc.
View 2 Replies
View Related
Mar 31, 2004
I am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
View 4 Replies
View Related
