I have a sales sheet that records every sale as it comes in. I want it to copy the relevant information (only that sales reps sales) into each sales reps individual sheet automatically so that I can send each rep his own sheet. Can this be done? SEE ATTACHMENT
View 2 RepliesSo, I'm setting up an accounting book. There's a master sheet that needs to pull from the other sheets that are made. The problem is, with each new sheet that is made, I have to update the formulas on the master. I would like that if I made a new sheet, the master would automatically pull from it.
Example:
Master pulls renter fee info from May 3rd, May 7th
I create sheet (May 15th)
Master pulls renter info from May 3rd, May 7th, May 15th
I am trying to sort information on my worksheets by date, oldest to newest however this does seem to be working on the workbook i have attached.
View 3 Replies View RelatedI need to group some invoicing values by date, but can't even sort the first column by date, so am going nowhere fast. I have the following type of data -
01/07/2013 25
02/07/2013 50
09/07/2013 62
08/09/2013 95
All dates are within 2013, and I need to be able to give weekly totals, on 18000 lines of data similar to above, but have no idea where to start - and can't even sort by date as it stands!
I have created a spreadsheet that I must fill out daily. this worksheet has averages that must be automtically shared with a "master" worksheet.
for example
information from sheet2!b23
information from sheet3!b23
information from sheet4!b23
etc...
must automatically be transfered to
sheet1!c6
sheet1!c7
sheet1!c8
etc...
in that order
at this time I am typing in =sheet2!b23 on sheet1 everytime, I want to avoid this.
If you are asking yourself why? I dont blame you but it needs be this way so I can average each days spreadsheet.
I have 25 sheets in the workbook and a combobox on the main page, The combobox references a range of 1-25 that represents the 25 hidden pages. right now i can get the sheets to unhide one at a time based on the selection e.g. combobox option 1 will unhide sheet 1 but the sheet are representing sites in a design so i need to have the option to select multiple sites in the combobox option so for example if i select 5 then sheets 1-5 should unhide. I hope I've explained that clearly.
The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.
I'm wondering if it is possible to make a table where I can just enter the reference number and excel can fill up the remaining cells with data specific to the reference number from a website. What I mean is that I would like to put in company registration number and hopefully it will be able to pull in other data from companies house website about the company.
View 2 Replies View RelatedI use a spreadsheet (FY-2012) to track approx 700 work requests. I enter the work request number (name of the spreadsheet) in column A, which is hyperlinked to the actual work request spreadsheet. I would like to automatically fill in this cell with the information entered in cell J42 of the work request entered in column A. Cell A5 of the tracker "FY-2012" indicates Work Request "BB-12003". Cell D5 of the tracker "FY-2012" indicates the completion date. I would like to extract the completion date entered in cell "J42" of Work Request "BB-12003" and have it automatically fill in Cell D5 of the tracker "FY-2012".
View 1 Replies View RelatedI have created a spread sheet that uses the LOOKUP function to pull information from elsewhere in the sheet and automatically fill in a cell so that I can save time.
The spreadsheet has several columns with a single word or number but the column used for the lookup contains 2 words, 1 number and a “#” sign. I have used the “CONCATENATE” function to create a column that has all the words, the number and symbol and then my formula says to LOOKUP the value in the created column and return the number associated with that LOOKUP value. The problem is it will not give me the correct value. All cells with the formula return a value from the same row in the LOOKUP table no matter what the LOOKUP value..
I have used this exact same formula (copied and pasted it) in another place in the table with the LOOKUP information added by hand, not generated with the “CONCATENATE” function, and it works fine.
I'm trying to build is a master Excel sheet for a company-wide budget tracker, where our supply person enters in information on individual orders. Those orders would be broken down in separate worksheets based upon department.
So, let's say you have three departments. Each department has an identifier code (Human Resources would have HR, Operations would have OP, and Research & Development would have RD.) Each order number is prefixed with the department's code, then the other columns deal with dates ordered and received, cost of the order, and any notes on the order.
What I'd like to be able to do is to have each order be automatically filtered out to secondary worksheets, based upon which department the order belonged to.
I have seen a macro that allowed one to push a button and break data out like that, but the problem is that it created new worksheets each time, and I want the department worksheets to stay the same (since each of those can expect to have starting budget figures updated by the supply officer.)
I have a customer data base going across each row about payments/address/DOB etc. When i go to alter some information in certain cells the rows become uneven and the information for customer B1 will be for C1 instead. I'm not familiar with excel but what i did notice when scrolling down i saw an outline of uneven rows.
View 1 Replies View Relatedi'm have a master spreadsheet of contact details and categories. i want to be able to pull contact information onto other sheets within the same workbook, based on category. example attached.
View 5 Replies View RelatedI have inserted the sheet. As you can see in Sheet 1 I have the Store, Address, Town etc, I would like a drop down box in D13 ( Store Name ) so when I select a store it will automatically fill all the info into D15:D19 Address, Town etc. The info will be pulled from Sheet 2.
View 3 Replies View RelatedI would like to Use two drop down list with two set of information to automatically update fields.
I have Job Titles (1-6) and Step (1-5). Each Job Title has a new pay rate and each step is an increase in pay. What I am trying to do is set up a drop down list where some one can drop down Job Title and step and have the rate automatically fill in.
I am able to get the Job title to auto fill the rate field, but am having trouble incorporating the step into the formula.
Here is what i currently have:
=VLOOKUP(H6,'Pay Rate'!$B$2:$G$12,3,FALSE)
H6 = Job tilte field on 'WorkSheet' (Sheet1)
'Pay Rate'!$B$2:$G$12 = Sheet2 and Step increases per Job Title
3 = the current column I was working with in order to make the rate appear
False = for exact info
I do not want to combine the two columns but keep them separate drop down list.
I want to make things easier for myself when filling in my worksheets by having certain cells autofill based on a product number.
So I have my main worksheet and I have my product information sheet. When I type in the product number in cell 13C, I want it to auto fill cell 13B with the product name which is located in collumn A on the product info sheet, but dependent upon that product number. Collumn B in my product info sheet has all of the product numbers. From there I would need 13G on my main worksheet to fill in with Collumn D from the product sheet, but again dependent upon that product number.
There are more that need to fill yet, but I can figure out what to do once I have a starting point with the above I believe.
I am trying to use visual basic editor in excel. I have all ready set up my user form where information can be entered, but I ave having trouble getting the information that is entered in the user form into the correct cells in excel.
I am wanting my information to enter the tables and then automatically move into the next available cells below.
Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("details of cars in stock")
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
If Trim(Me.txtreg.Value) = "" Then
Me.txtreg.SetFocus
MsgBox "please enter a registraion number"
Exit Sub
End If
ws.Cells(iRow, 1).Value = Me.txtreg.Value
Me.txtreg.Value = ""
I have created a form in Excel for my wife to be able to enter data. Once the data has been entered, a print is required, but, the form is large, it contains lots of formatting, a lot of "space" and a lot of color. I world like to (on another sheet) collect all the relevant text, without any formatting, space or color to produce a concise print.
View 2 Replies View RelatedIf 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 RelatedI have a graph which displays information from a vlookup.. The vlookup can bring either numbers which are in percentages format or ordinary number format.
The question I have is is it possible for the graph to automatically change format depending upon the information who is looked up..
If so how can this be done..
I am trying to get information from two sheets. I have added the lines myarray1 and mystring1. I also added myarray1 in the For Statement. I cannot get it to run correctly. If you take these two lines out, and the myarray1 out of the FOR statement it works for one sheet.
Sub test()
On Error GoTo notfound
myarray1 = Array("A", "D", "G", "H", "I", "End")
myarray = Array("A", "Q", "R", "S", "T", "U", "V", "X", "Y", "Z", "AB", "AC", "END")
For i = 0 To WorksheetFunction.Match("END", myarray1, myarray, 0) - 2
x = WorksheetFunction.Match(ActiveCell.Value, Sheets("phones", "computers").Range("a:a"), 0)
mystring1 = mystring & Sheets("phones").Cells(1, myarray1(i)).Value & ": " & Sheets("phones").Cells(x, myarray1(i)).Value & Chr(10)
mystring = mystring & Sheets("computers").Cells(1, myarray(i)).Value & ": " & Sheets("computers").Cells(x, myarray(i)).Value & Chr(10)
Next i..........................
I have a workbook that has close to a hundred sheets in it (see attached). I need to know how to get all of the information onto one sheet in separate columns like this: EMPLOYEE NO| HIRE DATE| NAME| VAC AMOUNT|WEEKS|VAC DATES| # OF HOURS|AMOUNT|PPE
If you review the attachment you will see how the sheet is read from left to right, so I would need the have the information in that order.
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
ABCD 12345
ABD 12345
ABCD 12349
ABDF 12349
ABCD 12358
ABF 12358
How To Make the Table Sort Automatically Without using Sort Button? For Example, I Changed one of the Cell's Value. Then The Table Sort itself.
View 2 Replies View Relatedi have a load of data that i dump onto a sheet and i would like to sort automatically with out the need to do it via data and sort
I have attached an example
Can it be do within a lookup?
I am now trying to write a macro which is a little over my head and was hoping some one may have some idea how to do it or have some code that is similiar and I can try to customise it myself. So here goes.
Basically all that happens is I paste data into a work book that always has two columns. The first column is a list of names and the second column is a list of numbers. I need to sort that information automaitcally every time it is pasted into the work sheet as follows.
1) Once any information is pasted to sheet 1 run macro automatically with no user interaction
2) All numbers must be rounded up or down first before sorting. If it is say 2.4 then it would become 2 and it it was 2.6 it would become 3.
3) Then move the numbers only from a specified column from sheet1 to sheet 2 and split them up into seperate columns based on the following results.
a) 0 - 3 gets moved to column B
b) 4 -7 get moved to column C
c) 8 - 10 gets moved to column d
4) Delete both columns from sheet1 after this is run so the user can re paste new information
5) If a user does this again then do the same but move into a new set of columns e f g and so on until the work book ends.
Here is what I want to accomplish:
I have a spreadsheet with data in cells A36 to G52. In The D column (D36 to D52) I have a date in there. I would like to have this column sort in ascending order automatically, but also have it sort the other data within the same row with it.
I have tried a couple of VBScripts that I have found in other posts that are similar, but I just get errors when trying it so I assume that I am not putting something in correctly.
Can anyone help me write this functionality? I have other data in a similar setup on the page that I also want to sort automatically but the script for this first part should be just a copy and paste, and change a couple of values....at least I am assuming so.
Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered.
View 11 Replies View RelatedI am trying to get a list to sort automatically. I am running a competition over an 11 week period and entering weekly data that is totalled on a separate "Totals" sheet.
This "Totals" sheet, in turn, feeds a "Scores" sheet which I want to automatically sort in descending order of score in order to show the current positions in the competition.
The data in the list isn't entered directly but comes from formulas that reference cells in other workbooks.
I have found ways of sorting a list when you enter data into the list itself but what I need is a macro that will update the sort order automatically whenever a new value is entered in one of the other workbooks.
I am currently in the process of creating a league table using Excel, and was wondering how I could get the teams to automatically arrange themselves in order of total points?, without using macros, is any formula capable of this?
View 9 Replies View RelatedI have (what seems to me) a somewhat complicated request. I’m trying to do a custom workbook without using VB (if possible). I’m trying to design a file list that can be updated easily by anyone. The new row of information needs to be entered (by a data form maybe?) and then automatically alphabetized by ‘ case name”. See below. I don’t know Excel formulas that well. This seems fairly easy, but I am at a loss how to do it.
Case NameCase #Open Date Closed DateRep
1Apple, Aaron2006-45 12/8/06 Tom
2Apple, David2004-02 01/14/04 Bill
3Bennett, Tim2006-0411/28/06 Bob
4Carter, Jimmy2005-23 04/22/05 Chris
5Carter, Tim2000-11 08/02/00 Jim
6Ford, Gerald 2007-206/06/05 Jill
7Gold, Bill 2006-7803/12/04 Pat
8Hill, Jim 2006-7703/18/04 Amanda
9Janes, William2005-6803/14/05 Dave
I would like to enter a new record (“case name -e.g. Jones, Smith, etc.) , with all the other corresponding info (Case #, Open Date, Rep #) and have excel automatically insert it ALPHABETICALLY by case name into the sheet and also, if possible, renumber. I know I can add the new record it to the last row and the Sort by case name, but I wanted to have others do it, and when they try to enter and sort the spreadsheet it always gets messed-up.