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 RepliesMy workbook contains several worksheets each recording the results of equipment tests - one sheet for each piece of equipment. Rather than open each worksheet to check the date of the last test, other 'overview' worksheets pick up and display the last test date from each equipment record using the formula =MAX('sheetref'!A15:A500). Is there a better formula to do this?
Some items of equipment are tested at more than one frequency, ie Monthly, Yearly, etc. Therefore, the entry on row 15 of an equipment record may be column A 28/10/08 column D 'M' and row 16 column A 29/10/08 column D 'Y'. The above formula will only display the last test date, irrespective of frequency. There is a seperate overview sheet for each test frequency therefore, I need to modify the formula so that the date it copies from the equipment record worksheet to the overview worksheet is the date of the last test for the specified frequency.
I have the following code below. I would like to amend the code to retain the previous entry in the input box and amend this if the need arises
Code:
Sheets("data").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = InputBox("Amend details if different from data in inputbox")
I am trying to resolve a calculation issue where I want to sum accross columns depending on an entry in the column immediately preceeding. The layout is an Attendance sheet, The columns are for the days of the Month ( 1 - 31 ) and the rows are the Months. There are 2 columns associated with Each day. The first column is for the type of Time Off ( Vacation, Sick, Personal, etc ) the column next to it records the number of Hours some one took off. The work book has a Sheet for Each Employee and a running total needs to be maintained for the amount of "off time" each employee takes by the various time off categories. I have tried setting up range names but this won't work as there will be multiple sheets. I believe the problem is the mixture of Text and Numeric data but could not resolve.
View 3 Replies View RelatedI have a column of data (Column T) which has about 10 different words in it (varieties of fruit). What I want to achieve is based on the data in column T I want to fill Column U with information, let me put it in an example.
Column T has the following words in 2000 rows. Thompson, Crimson, Sable, Superior, Midnight, Ralli.
If Cell T4 for example has 'Thompson' or 'Superior' I want cell U4 to say 'white', but if cell T4 has 'Crimson' or 'Ralli' I want it to say 'Red'. If it has Midnight I want it to say 'black'.
I think this can be done using a vlookup maybe? I don't have to go down 2000 rows to fill this information in.
I have a large database of customers. I would like to edit this database so that it only lists those customers that have an email address listed in that particular column. I can then save that as a separate list to upload to my marketing campaign.
Is there an easier way than just deleting entire rows at a time? About a 2,000 name list with maybe 30% having email addresses.
I have a market data linked to excel through DDE link so prices are updated real time.
Cell A5 contains last traded volume which updates whenever a trade is done.
I would like to SUM/ADD all last trade volume in cell C5. Is there any function or macro can help me to do this?
I have data in excel that has some information the same with a unique field. See below:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
[code]...
I want it to consolidate all of the like information but add on the codes in separate columns like this:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
Code 2
Code 3
Code 4
[code]...
Also, sometimes the same code is used multiple times but i want any duplicate codes to show as separate codes.
I currently have a large spreadsheet that multiple people fill out. Each person fills out all the information in a row. At the end of the row, I would like a button that says "Generate Form" so that when clicked, a new sheet automatically opens with a template form that I created and is already filled out with the information that was just inputted into the spreadsheet. Also, there are four different template forms that could generate. For example, there are forms A, B, C and D. If the user inputs "B" into the first column of the row, then when he goes to click "Generate Form", a new sheet is created with all the information filled out in Form B.
View 3 Replies View Relatedlet's say I run a macro from a button on sheet 3, macro process on sheet 10. Is there a way that the macro would automatically go back to sheet 3? Like a "Back" button on IE. I can't code Sheets("sheet 3").Select because I want it to do the same thing on sheet 4,5,6...
View 3 Replies View RelatedI am running Win XP and Excel 2003.
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test()
Dim i As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = LR To 2 Step -1
If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete
Next i
End Sub
After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive.
Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
I need a formula to automatically change the summary column according to the month we are in.
Ie:
Last Months Data
Nov
Dec
Jan
Feb
1
8
1
7
4
7
9
2
9
'Last Months Data' column should show Dec. However, as we move into February and I complete the 'Jan' column, I would like 'Last Months Data' to automatically change to show Jan's data - is this possible?
I was previously told to use the following formula but this would automatically select the current months data and not the previous months data which is what i need - =INDEX(B2:L2,MONTH(TODAY()))
I have options buttons in a userform, first is "$" the other is "€"
When somebody chooses one of them, it writes the choice to a cell in the data sheet. But when clicked on the next userform I want some of the texts automatically change the currency depending on the choice made in the previous userform.
The formula below was is example
=""&Data!$A$2&" / m³"
=""&Data!$A$2&" / kWh"
Is there a way you can copy formulas to the next line when a new entry is automatically added to a spreadsheet via a form?
View 1 Replies View RelatedI've got a master spreadsheet grabbing data from 12-13 different tabs/spreadsheets.
One of those has a table, and I was wondering:
Is it possible for the master spreadsheet to automatically add a new line every time someone adds a new line and creates a new entry in a different spreadsheet?
In other words,
If there is a table in the master spreadsheet, and someone adds a new line, is there a formula which allows the master spreadsheet to add a new line in order to autopopulate?
Is there a way to automatically move cells down after I entered data in it? This way I do not have to scroll all the way to the bottom of the list to add new data.
View 5 Replies View RelatedI'm trying to figure out how to create a database on Sheet 2 by filling in the info in a template created on Sheet 1. I have seen it done with the addition of a button within the Excel sheet itself, and you simply click it to send your information to the database page. I am thinking it is along the lines of OLAP or recording macros
View 8 Replies View RelatedI 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 Replies View RelatedI 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 two seperate lists. One on Sheet 1 and One on sheet 2. What I need to do is take the list on sheet 2 and compare it to the list on sheet 1. If any of the items appear on sheet one, I need to delete those items form sheet 2.
So in the end the only items left on sheet 2 will be items that dont match any items on sheet 1
How do I get data validation to automatically add an entry into its database.
Ex.
If a cell if formated for data validation and I make an entry thats not currently in its database, it enters it into its database.
I already have various dependent validation lists established using the INDIRECT function.
Now I want to figure out how I can automatically change the selected values given the selected list. For example:
Cell D11 is a validation list with options: Boat/Car
Cell D15 is a validation list where if D11 = Boat the options are Catamaran/Sailboat/Tugboat. If D11=Car the options are Convertible/Sedan/Midsize.
The problem is that if D15 is preselected as Sailboat, for example, and you go back and change D11 to equal Car, cell D15 remains an option from the Boat validation list. I would like it to automatically update as the 1st item on the car validation list.
I have multiple worksheets of computer equipment, each worksheet is a group/department. Column H is the serial number column and the entries have to be unique. I have managed to create the code below which does find duplicates across worksheets.
When error message pops up about which sheet the duplicate already exists on, the duplicate entry is deleted and the cell is blank but the error checks again and reports the blank existing on another worksheet and then it is stuck in a loop. How can I ignore the blank or null.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Integer
I = Sheets.Count
If Not Intersect(Target, Range("H2:H200")) Is Nothing Then
Do Until I = 0
If Application.IsError(Application.Match(Target, Sheets(I).Range("H2:H200"), 0)) Then
Else
MsgBox "That entry already exists in the " + Sheets(I).Name + " sheet"
Target.ClearContents
End If
I = I - 1
Loop
End If
End Sub
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 currently have this formula to deal with this:
=SUM(J5:INDEX(J:J,ROW()-1))
However, whenever I delete the top row...I get a reference error and have to re-adjust...
I currently have this formula to deal with this:
=SUM(J5:INDEX(J:J,ROW()-1))
However, whenever I delete the top row...I get a reference error and have to re-adjust...