Should be simple but I can't get this to work,
I have used =value(a1) that prevents any formula, but it also prevents a zero being entered.
I have amended trying and/or statements in the validation but then it allows zeros and formulas!
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.
View 5 Replies View RelatedI 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.
When users paste data into cell , I need to protect data validation/ conditional formatting etc. How can one do this?
View 14 Replies View RelatedI 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.
Paste Restriction on Validation Cells.xlsm‎
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.
View 4 Replies View RelatedI'm looking for a formula which will enable typing (in cell A1) positive numbers between 0-1000000 but will prevent the user from leaving that cell in an empty state. Empty cell means: cell with space(s) or deleting the legitimate value that is, already, in it).
I must emphasize that I know how to handle this by VBA code and/or by using a "named" pre-defined list of values + un-checking the "ignore empty cell" option - but I would like to know, once and for all, the solution WITHOUT using Macro and/or 'List' (if any..)
I tried that, without success:
=AND(A1>=0,A1<=100000,NOT(ISBLANK(A1))
I have a data entry worksheet where a user may use the space bar to delete a wrong entry instead of the delete key. How can I allow this without triggering an error message for a cell which is valid for blanks or a positive number including 2 decimals? The checkbox for "Ignore Blanks" does NOT ignore this is the cell is blanked with the space bar.
View 2 Replies View RelatedI set up a spread*** with drop-down menus for some of the cells. The user can only select values from that list and an error message pops up when something is typed it that is not in the list (via Data Validation
Error Alert).
So this works all fine ... except if the user copies and pastes a cell with a different drop-down menu into a cell overwriting the existing validation.
I cannot totally lock the work*** and if I lock the cells with the drop-down menus nothing can be changed. So how can I prevent this from happening? Is there a way to block all copy/paste operations that are copying the validation? I know that with the Paste Special I can have cells copied without the validation.
I want to prevent user from entering duplicate text or numbers in a cell using the Data Validation.
View 9 Replies View RelatedThis is my data validation formula:
=INDIRECT(VLOOKUP(H104,cat_opt_Table,3,FALSE))
Some of the lists will have up to 200 lines in them and I'd like to find a way to simply to jump to the first letter entered in the data validation cell.
Does the list need to be alphabetized?
Could it be something that is entered into the data validation box? This spreadsheet has data validation in 35 columns.
I have a sheet with 2 tabs. On the first tab is my data and the 2nd tab is the formula.
This is my formula;
=IF(ISERROR(VLOOKUP(A3,Data!$A:$AD,23,0)),"",(VLOOKUP(A3,Data!$A:$AD,23,0)))
When I insert a new column at 'A' the formula changes (as below)
=IF(ISERROR(VLOOKUP(A3,Data!$B:$AE,23,0)),"",(VLOOKUP(A3,Data!$B:$AE,23,0)))
How do i stop the reference changing from 'Data!$A:$AD' to 'Data!$B:$AE' when inserting the column?
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
View 2 Replies View RelatedI want to restrict the value entered on a sales sheet to force the value to be over 15% margin. In column M you enter a value in column N it report the margin. I want to force the value in M to give a minimum 15% in column N or report an error.
View 3 Replies View RelatedIs 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.
View 14 Replies View RelatedWhat 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 am working on a resource management type workbook. In the first sheet,(Project Assignment) managers can enter staff, staff type and hours needed for the next three weeks. In a separate sheet,(Total Hrs per week) I have formulas set to total the hours entered from the first sheet for each resource - using the formula =SUMPRODUCT(--(staffassignments=$B4),--(Week1)) for each week, for each resource. That woks fine.
What I would like to do, if possible, is to present a type of data validation, dialog or popup when the resource's "total hrs per week" total = 40 hours. Different managers use the same resources, so one person may have time entered in multiple times for different projects in the Project Assignment sheet.
Is it possible to use the totaling formula (=SUMPRODUCT(--(staffassignments=$B4),--(Week1))) and an if statement in the data validation or conditional formatting to let managers know that the resource is fully utilized?
I currently am using Data Validation drop-downs (which are identically referenced) in a number of cells (From J10 to J19). Each entry refers to a Crew departure &/or arrival time (based on an Aircraft schedule) and crew Subsistence & allowance ($17 per day). Referenced from “K10:K19” is USD currency:
DAYS:CURRENCY:
“J10:J19” “K10:K19”
“FULL-WEEK”(References 7 Days) = “USD 120.00”
“SATURDAY (DEPART)”(References 2 Days) = “USD 35.00”
“SATURDAY (ARRIVE)”(References 6 Days) = “USD 100.00”
“TUESDAY (DEPART)”(References 5 Days) = “USD 85.00”
“TUESDAY (ARRIVE)”(References 3 Days) = “USD 50.00”
I am trying to establish a way to specifically; select a particular day in the Data Validation drop-down menu (J10:J19) and a formula automatically converting the result to “USD Currency” for each of the 5 alternatives?
So in other words, each data validation cell will have 5 matching formulas pertinent to each specific orientation (Day)?
E.G.Select “FULL WEEK” from the drop-down option and “USD 120.00” is revealed / converted?
Select “SATURDAY (DEPART)” from the drop-down and “USD 35.00” is revealed?
... “SATURDAY (ARRIVE)” = “USD 100.00”
... “TUESDAY (DEPART)” = “USD 85.00”
... “TUESDAY (ARRIVE)” = “USD 50.00”
The closest I have managed (with no real success) is as per the following formula:
IF(T26="FULL_WEEK",X26,IF(T26="TUESDAY_(ARR)",X27,IF(T26="SATURDAY_(ARR)",X28,IF(T26="TUESDAY_(DEP)" ,X29,IF(T26="SATURDAY_(DEP)",X30)))))
I would be so incredibly grateful if somebody could help me (in laymen’s terms)?
I have a table created that uses countifs formulas to pull data. I want the data to change based by client - so I created a drop down list with each client's name, and thought I could add a criteria to the countifs formula based on the client's name in the cell- but I get a value error.
View 9 Replies View RelatedI 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‎
What I have attached is spreadsheet I've built to quickly generate an army list for a tabletop game. Anyone can click on the Force Organization Slot (FOS) and choose the type of unit and from there, select the specific unit name attached to that FOS. Each individual unit has a list of options/upgrades currently attached to the sheet from cells AD through AX and the points cost listed next to each upgrade.
For example, when selecting the Rhino unit from the Dedicated_Transport FOS, there should be 4 drop down lists of selectable upgrades (Storm Bolter, Hunter-Killer Missile, Dozer Blade, and Extra Armor). Currently, I have only named the ranges for the Rhino and Razorback units in order to get this working properly without having to go through the entire list of units and change names/create ranges/ect.
What I'm aiming to do is to create an individual drop down list that is directly related to the Unit Name cell in order to populate a complete and selectable upgrade list for each unit. I have played around with some functions that work in the spreadsheet (see cells X10:X12) but do not work properly with Data Validation.
Oddly enough, when I enter this formula under Data Validation:
=IF($A$3<>$AD$1:$AD$182,VLOOKUP(A3,Options,2,FALSE),"")
I do not get an error, yet the drop down list does not allow me to select anything as I thought it would.
My Table has a data validation list in one row of a table. And different formulas in different rows.
The Table is expanded only into columns. Which means, there will be no new rows...only columns will be added.
If we enter text into the header cell in a new column, the whole new column gets formatted, but the list and formulas do not auto fill into new columns.
Of course an easy way is to copy and paste a column...but is there a way to auto fill lists and formulas into columns of the table? Similar to how the rows get autofilled?
i'm trying to get data added in one sheet of a workbook to automatically be entered into another sheet. such as a monthly, Quarterly and Annual balance sheet.
View 3 Replies View RelatedI would like to sum results, all but #NA results. For example,
=Sum(I8:I10)
How do you manipulate the sum formula so it ignores the #NA 's? zip attached.
I've a lot of formulas (made in vba) in a Sheet. The problem is that even if the formulas all work as they should from the beginning, somtimes when I'm running some other macros, the formulas instead shows some error message. When selecting the cells and pressing enter, the formulas once again shows the right answer. Since it's extremely inefficient to select and press enter at each cell, what I need is some way to update these cells. Anyone know a quick way to update all formulas in the sheet?
View 6 Replies View RelatedI am working in an excel file with many sheets. I have vlookups on several pages that refer to a different page in my file. However, I am finding that the vlookups are not autocalculating. They are fixed on a value until I click into the cell and hit 'enter'
View 9 Replies View RelatedThis site provides a partial answer to my problem- http://www.j-walk.com/ss/excel/tips/tip98.htm
That link worked for me but there's a problem with it, it does not work if a table has different types of validations.
I need to calculate the amount of time my daughter will be in daycare which varies each week. I have to give the same sheet that I have hidden calculations on to the center. I have to have the time she arrives at 7:15 AM (Cell A1) however they don't start to bill until 7:45 AM (Cell A2) and continues until 11:30 AM (Cell B1) and need C1 to be total hours between A2 and B1
I need a formula that will calculate the hours between 7:45 AM to 11:30 ONLY if cell A1 has a time entered in it. If A1 is blank then it should calculate 0 hours.
Example (WITH something entered in A1)
Arrive Time End Billable Time Total Billable Time
A1 - 7:15 am to B1 - 11:30 am C1 - 3.75 Hours
Start Billable Time
A2 7:45 am
Example (WITHOUT something entered in A1)
Arrive Time End Billable Time Total Billable Time
A1 - to B1 - 11:30 am C1 - 0.00 Hours
Start Billable Time
A2 7:45 am
If it has to be in 24 hour time is there a formula that I can enter in another cell to convert 12 hour time to 24 hour time again ONLY if A1 has something entered in it?
I am in need of 2 validation macros (formulas) to enter into the validation criteria formula box when creating a custom validation.
1. A formula that will generate an error if there is a space in the text entry. Example:060107_Halo3CG prev.mpg would be an invalid entry due to the space after CG.
2. The same formula as above but this one also needs a 50 character limit added to the criteria.
Is there a way to have a formula or a VBA code associated to the choice of any value in a Validation list.
For example is there a way to have lets say in A1 a validation list and to have B2 returning the username of the person of who will select a value in A1?