I currently have a monitoring spreadsheet which my staff enter data into each month. There have been some problems with staff entering data wrong so I have decided to build a userform to prevent errors. I have 2 combo boxes one for selecting a month of the year and another for a particular call the user must enter data for. I want the userform to enter the data from the userform into specific cells in a specific area. e.g. If a member of staff entered data into the userform for February, Call 2 then it would appear in the relevant boxes.
I have created a userform with 3 text boxes. It also has three buttons - clear, Cancel and Generate record button.
User form takes entries in the three text boxes and on clicking the Generate Record button, the values of the three Text Boxes are inserted in Column A, B and C of sheet1. The columns keep on populating with new data on each submit in the row below the last record.
Now it is required to enter data only if the value entered in TextBox1 is new and has not been entered previously in column A. If textbox entry is already aviailable in column A, a message box of 'Record available' shoud be prompted and the text entry must not be allowed until the data entered in TextBox1 is not unique.
Is there any neat little way of preventing a character say '=' from been entered in a cell, kinda similar to the validation but for a 'dont allow' function.
What formula can I use to accomplish the following, if it can be done.
If B9 is 100% all the rest of the % cell inputs would be 0%
If B9 is less 100% ,lets say 50% all the rest of the % cell inputs would be less than or equal to 50% until any sum of those cells equal to 100% in which case if I enter 50% on B9 and 25% on D9 and 25% on F9 their total sum would be 100%.
If I try to enter a % on H9 it should not allow me to do it because then the total sum on O9 would be more the the original amount on A9
I have a created recording sheet (attached) for the purpose of recording moths. I send this sheet out to those recorders who live in my county and at the end of the year they send the filled in sheet back to me. Over time I have put safeguards onto the sheet to ensure that I get the cleanest data returned to me. However, there is still one area which is giving me problems - users entering duplicate records.
Some formula which will stop recorders from entering those duplicate records. There are many columns on the sheet which recorders have to fill in with information, but as far as the duplicate records are concerned it is where the same information is entered into columns C, F and J that I'm having a problem with. It would be great if, when the same information was entered in these columns that the affected cells were highlighted in some way and if an error message appeared saying 'Duplicate record, either delete or alter grid reference'
I have entered two test duplicate record on the 'macro moths' sheet of the attached file : Test Recording sheet.xls
Using data of unknown origin in Excel I found Isblank was giving a FALSE result on some apparently blank cells while giving a TRUE result on others.
Even if I used TRIM and CLEAN functions on the offending data and pasted the resulting values back the the original locations, the Isblank result was still FALSE. Also, the font colour was not set to white or transparent.
However, if I selected the cell with the 'invisible' data, clicked in the Formula Bar and pressed Enter without entering any new data, the problem disappeared for that cell. The problem also disappeared if I selected the problem 'blank' cell and pressed delete.
Can anyone explain this 'invisible data' and tell me how I can detect it using a function or formula?
I am trying to create a "Master Sheet" where I enter in the column data and after I have entered my data for each row, I can select the button which toggles the macro to run. I have it built to build new sheets as new clients are obtained. My problem is after I have a sheet that has client's data I cannot get new data to add itself below the data that is already there. I want each client's sheet to keep adding rows as more data comes in. My current macro is :
Have attached a small 2007 excel spreadsheet to help explain my needs. I would like to know if there is a function that when data is entered into column A it is then transfered to column B, B data transfers to C, C to D, D to E etc. with the K data falling off (10 Columns). I need this process to happen even when the new A input data is of the same value as the last A input data.
I have a main "ControlSheet" with a list of client and info in Columns A,B,C and E
In Column D of this "ControlSheet" I enter the Staff member who the client has been allocated to (e.g "Staff1")
A B C D E
1 Ref Data1 Data2 Staff Date Client Allocated
2 102 1000 10001 Staff1 01.06.2013
Each staff member (there are 7) has their own tab set up (e.g Staff1, Staff2, Staff3 etc)What I would like to achieve is when I choose the relevant staff member in column D of the "ControlSheet" (Lets say D2) I would like the Data from A2,B2,C2 and E2 of the "ControlSheet" to be copied to the next available/empty row of the Staff members own tab.
So in this example (above) A2-E2 would be copied to the "Staff1" tab as that is the staff member chosen If I was subsequently to change the Staff member chosen on the control sheet from say "Staff1" to "Staff2" I would then like the Data or Row to be removed from the "Staff1" Tab and added to the next row of "Staff2" Tab.
I am trying to secure a worksheet using data validation. I want to prevent users pasting over data validation fields, however I want to allow users to be able to still use the copy/paste feature as long as the data is valid.
Attached is a sample that I found on the forums. The current code does not allow any paste functionality.
I work in a correctional centre. Some of the correctional officers have little training to use Excel or other programs, but are required to complete spreadsheets on line. Although we have heavily protected the worksheets and locked cells, we find that in some cases the staff can still mess up the worksheets by copying and pasting data inappropriately.
Is there a way to turn off the ability to paste data into the worksheet, and to force each field to be completed manually? This would eliminate the problem.
I wish to prevent the use of the " ' " apostrophe in an entire column and I was wondering if this can be done with conditional formatting including a message of some type.
This has a macro assigned that copies the data and pastes it into another worksheet at the next available row.
Now this works fine, but I want to be able to identify if the data already exists in the other worksheet then prevent it from being pasted and give an error message that the person already exists in the database.
Above is what I hav already, I was thinking it would help if each card had an additional field added that told you if the contents were valid. This could also say empty if all fields were blank. This field could for example use an if statement to check that you have entered a VS, a start date a project name. It could also check that if you have entered a completion date you also enter review loops. This could be in large red text so it is obvious to someone completing a card that they have not yet filled in all necessary data.
I have a file with numerous data tables that slows down the file significantly due to it's large size. I would like to set the calculation method to Manual, but also be able to manually turn the tables on and off so that they don't slow the file down when I hit F9 to calc. Is there a formula or method to accomplish this? BTW, I can't use VBA.
I am adding up the value of a range of cells in one cell. What I want is If the value of the cell which is adding up the values of the range of cells exceeds a particular number it should throw an error.
i want to import data from sheet2 a1 from sheet1 a1 continously. now problem is when iam importing i shouldnt loose my previous data. eg: at time 11 when i import data to sheet2 a1 from sheet1 a1 with value 9. and at time 12 when i import data to sheet2 a1 from sheet1 a1 with value 10. value 10 will be over wrting value 9. here i shouldnt loose value 9. i want values comming afterwards should come to next row.
I'm using Excel 2003. I have about 190 rows that I use on any given day to enter start times & end times. I calculate the difference in Column E. Is there a formula that will calculate the average time as I enter them in the rows? Some days may have only 100 entries, other days may have as many as 190. I don't want to keep adjusting the average formula for column E.
I'm putting together a workbook to keep track of income generated from Contractors (I work in recruitment). I have an existing system that displays margin, hours worked and income (margin * hours) but what I am looking for is a neater solution that would still have the margin column, but when hours are entered into a cell it is factored by the margin and displays income in the same cell.
So to be sure I am describing correctly, I am after a formula that will enable me to
1. Enter a value into a cell (for the hours worked - lets call this B1) 2. Multiplies this value by another cell (a fixed amount, the margin - lets call this A1) 3. Displays this result (B1 * A1) in cell B1, the same cell as the hours were entered in.
I am trying to get two text boxes on a userform to populate a third box on the form when the user inputs the data in both of the first two boxes. I am pretty sure i need to use a change event, but I am not sure how or what triggers them. so I have:
[Code] ....
but they don't work when I put data in VSShortPrem and VSLongPrem textboxes. Do I need to call them to the userform module?
I have a file in which records are saved in chronological order. What i want is when i click on a button it should prompt me for a date and when i enter the date it should retrieve the data from the database sorted in chronological order from the date i have entered.
I got a table, some columns are variable data you have to put in by your own and I got some columns with only formulas. After entered the last variable data I want excel to add a new row with the same formulas and format as the other rows in the table.
i currently have an email button setup, so when the person has filled in the sheet and clicks the button it sends the active worksheet to the specified email address in the code
i need an extra step in this code to check that data has been filled in, in certain cells before the email is sent
for example i have numberous cells but in cell i17 if something is entered then there must be something entered in cell i19. If there is nothing filled in cell i17 then the data validation can ignore this. Basically it needs to check that corresponding cell(s) are filled in "if" the first cell is filled in (make sense?)
i would also like it to check certain cells are filled in (mandatory cells)
here is the email button code, is there anything i can implement in that to produce the above results? or if this cannot be achieved maybe a warning box to pop up before it sends to say "Please make sure everything is filled in corrently before submitting" (something like that)
I have a command button [Start] that when clicked, will enter the time the process started. I then wish to have the cell locked so the time cannot be changed after it has been entered. I tried this code:
Private Sub CommandButton7_Click() Range("I5").Select ActiveCell.FormulaR1C1 = Now() Cell.Locked = True End Sub
But am getting an error for the "cell.locked = true"
Any proper code to lock the cell after the data has been entered?
I have an Excel spreadsheet that I use to schedule employees. I use this to assign sections to various employees. I was wondering if there is a formula that will restrict me entering the same "section" (or data) on the spreadsheet twice. The page is rather large and sometimes I can enter the same section without knowing it until I manually check it.
There are two worksheets: Finances and Summary. On Finances, there is data input for years, quarterly. There is a cell on Summary that depends on which year is input first, in which case the cells in the formula SUM("cell1:cell4"))/4 is currently changed manually by the user by just checking to see which year data is input first on Finances. I need a macro or a formula function where the workbook finds which year is being used on Finances first, then changes the cells in the Summary formula accordingly.
I am trying to create a simple IF/THEN statement to display profit margin for an order form. I currently have the margin formula set at (1-H14/I14). How should I structure my IF/THEN, to where it displays nothing in the cells, that are empty?
