Formula Protection For Cell Entry
Feb 11, 2004
I'm trying to set a cell so that it has a certain value based on other cell(s) information. I can put a formula into the cell to do that, but I also want to make the cell available for user input, preserving the formula should the user change their mind and delete their input.
Basically, it's a cell with a protected formula that can be overridden with user input, then if the user decides to take their input back out, the formula kicks back in.
View 9 Replies
ADVERTISEMENT
Jun 28, 2007
I making a data entry form that sereval employees will be using. But what I need is data quality protection. My idea is to have a userform with a combo box. The employees would select there name from the combo box and then enter there personal password to get access to the data entry information, which will appear if the password is correct but kept hidden if password is incorrect. I have a example.xls without any code contain in it.
View 2 Replies
View Related
Mar 14, 2014
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 Replies
View Related
Jan 16, 2014
how can I prevent an entry in a cell even without formula lets say cells B1 , C1 , D 1 , E1
I want to prevent an entry in these cell and if you try an error message will display
View 1 Replies
View Related
Apr 25, 2007
I have a problem to solve
I have 2 worksheets named 'Products' and 'Order'
At the 'Order' i use 3 cells
Code
Pallets
Cases
When i entered a code to the cell 'code', it reads the code from the sheet 'Products' and then transfers to the active sheet 'Order' some items.
What i want is:
Because the user has to enter the quantity either to the cell 'Pallets' or
'Cases' i want to be locked the empty cell each time and to be used for calculations.
1st Eg.
The "45870"
Pallets to order : 50
because the user used the pallets the cell "Cases" must be locked and it must calculate the 50 pallets*100 so
Cases : 5000
2nd Eg.
The "45870"
Pallets to order : null
Cases : 5000
because the user didnt use any pallets the cell "Pallets" must be locked and it must calculate the 5000 cases/100 so
Pallets to order : 50
View 9 Replies
View Related
Jul 6, 2014
=IF(E14<=0,0,IF(N9="yes",MAX(E15*C15,30),30))
I am currently using the above formula and need to make an addition to it.
If D8 is greater than 9000 and less than 9999 then the entry will be 35 rather than 30. Any other entry in D8 would leave it at 30
View 5 Replies
View Related
May 19, 2014
Is there a way to incorporate these 3 boxes into one? So if I enter a figure into any cell, it will calulate it for the other two? Without overwriting the existing formula. I have attached the problem.
problem.xlsx
View 5 Replies
View Related
Oct 5, 2009
i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3
i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go.
View 9 Replies
View Related
Jun 3, 2006
Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case).
I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.
View 7 Replies
View Related
May 18, 2007
I need a formula that displays the word "Day" or "Days" depending on cell entry i know the formlula =IF((B12)>0,"Day","") to show the word day but i want it to show "day" if a 1 is entered or "days" if any number over 1 is entered.
View 2 Replies
View Related
Mar 20, 2014
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
View 4 Replies
View Related
May 28, 2008
say sheet 1 has 2 collums A & B
collum A is Names Collum B is Dates
A B
Bob Fenton 05/04/08
Rob Smith 05/06/08
Al Feth 05/08/08
Al Feth 05/18/08
Al Thomas 04/23/08
Rob Smith 05/23/08
Bob Smith 04/22/08
Bob Fenton 05/15/08
Al Feth 05/10/08
sheet 2 has unlimited collums in collum A is the name of the person in collum B to Z (or more) i would like a fomula that will search sheet 1 and return the dates for each entry of that name.
so sheet 2 would be like ....
View 9 Replies
View Related
Jul 14, 2007
I have a small piece of code which runs without protection but won't create the data validation list if protection is enabled:
Sub WorkSheet_Activate()
ActiveSheet. Protect UserInterfaceOnly:=True
End Sub
The code is:
Private Sub EqOther()
Cells(6, 9) = ""
Cells(6, 10) = ""
Range("i6").Select
With Selection.Validation
.Delete..................
View 3 Replies
View Related
Dec 13, 2006
I am looking for the code to keep certain cells from being rested on, they can be clicked on but in order for you to view the formula in the cell you have to hold down the mouse button otherwise it will move you over to the cell next to it. I was using another worksheet a while ago and was trying to view the formulas but when I clicked on the cells holding them it would move me to the next cell, the only way I could view them was to hold the mouse button down as soon as I would release it it would be gone! I do not want to protect the sheet, if there is another way to hide formulas I am open to all ideas!
View 4 Replies
View Related
Jul 7, 2009
i have an address that is all in one cell displayed like
101 hampton Court, Hampton heath, Hampton Town, Hamptonshire, HA01 1AS
but i need to have it split in to individual cells so
Cell A1 would be 101 hampton court
B2 Hampton Heath
C2 Hampton Town
D3 Hamptonshire
E5 HA01 1AS
each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle.
also i need it to work backwards ie
it finds the post code first,
then the county
then the town
as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
View 9 Replies
View Related
Sep 12, 2006
I'm protecting a spreadsheet with Excel 2000. For that i used the Excel functionality : Data -> " Validation"
Unfortunately, I can not protect the all sheet or workbook (Requirement). So even if the cells are protected by "Validation", any user can delete the cells using the key DELETE or BACKSPACE on the keyboard !
VBA Macro (which could be activate at each change on the sheet for example...) ? or if it's possible to avoid cells deleting with the "Validation" in the Excel Data menu ?
If it's a macro, it would do :
1 ) see if a user is pushing DELETE or BACKSPACE on the keyboard
2 ) see if the cell contains formulas
3 ) if yes, make a box appears to say that is not possible to delete this cell
4 ) return on the Excel sheet without changes
View 9 Replies
View Related
Aug 18, 2004
I recieve an error :
Run-time error '50289':
Can't perform operation since the project is protected.
When i try and run my code.
The code does add parts to modules and workbook events, but I need the code to be protected.
View 9 Replies
View Related
Nov 5, 2008
is there a way that i can stop people being able to edit certain cell in a sheet but still allow them to type in other cells as when i have tried diffrent ways it locks the whole sheet
View 5 Replies
View Related
May 21, 2009
I have a workbook with several spreadsheets in it. My 1st sheet is a summary sheet, the rows represents the sum total find each worksheet, normally i will just clk on the cell in the summary sheet and clk on the worksheet i need and clk on the total.
It always worked but suddenly won't respond. When i put= in a cell in the summary sheet and clk on the sum total of the other worksheets, it won't clk, it's like it has been frozen. I unlocked the cell protection but it still won't respond when i clk on any cells on the worksheets.
View 8 Replies
View Related
Oct 14, 2008
when i password protect my sheet it doesnt let my clear cells macro work on the cells i need it to clear why is this and what way is there around this completes my complex sheet.
View 2 Replies
View Related
Mar 1, 2012
Having the cell protected after entering the data. From the below table, i need when a date is entered and moved to next cell, the cell which contains the data should be protected. I need to this for the comments column as well.
Number
DateStart timeEnd TimeActual Time takenComments
123459132546132156135469654814846543
View 1 Replies
View Related
Nov 28, 2006
I have a cell with data validation: list, that allows values contained in named range. I want to disallow to delete contents of this cell (with 'del' key, or when i edit cell, delete text and then press 'Enter'). Is it possible to do with formula or VBA
View 4 Replies
View Related
Mar 27, 2008
I have 2 spreadsheets: in a Sheet1 I keep some values and in a Sheet2 5 names of users with special rights (range A1:A5). I want to have sure that anybody will change/delete that names without password. Only the user "Administrator" can do that(I check the name of the user using "Application.UserName"). Additional question: is it possible to still protect those cells if somebody turn off macros during opening file?
View 4 Replies
View Related
Oct 29, 2008
I have a worksheet with multiple entry boxes in a row. Depending on the value in one row, another row will either yield the result from a formula, or remain blank, and this cell needs to be locked. I was able to do this by first putting the formula into the destination cell, and then using custom data validation that says the cell value = "". So this prevents anyone from typing in that cell, no matter if it yields the formula result or must stay blank. What it doesn't protect against is someone deleting the contents of the cell. The reason I don't want to protect the cell is because I want people to be able to highlight the whole row past that cell and delete it all at once. So either using Data Validation, a formula, or VB programming I need to not allow someone to do ANYTHING to that cell, even delete the contents, without locking the cell. Hopefully it can be done, thanks!
For example, if E17 = "X", then K17 = INPUT!$M$28*J17. If E17 <> "X" then K17 is blank. Either way, I don't want any typing or deleting of K17. This applies for range K17:K91, and corresponding E17:E91.
View 14 Replies
View Related
Dec 3, 2009
Is there any way to change the pattern color of a cell (or a group of non-adjacent cells), say from No Color into Red if the cell(s) protection is locked (or unlocked)? Seems to me that the Conditional Formatting cannot work this way.
View 3 Replies
View Related
Sep 4, 2006
I work in the field of education and the spreadsheet I'm working will require pupils to enter answers into the spreasheet. When a command button (New Questions) is pressed I want the VBA code to switch the cell protection (locked) on or off depending on a random value in cell c2. If c2 = 1 I want f3 to be locked, if c2 = 2 then d3 needs to be locked, but not f3. I've attached a copy of the spreadsheet to this email
View 5 Replies
View Related
Jun 1, 2006
how to allow formating a text and cell in protect sheet ?
View 4 Replies
View Related
Feb 4, 2008
is it possible to restrict a cell in such a way that it is impossible to type data into it, but rather select data from a drop down, through validation?
View 9 Replies
View Related
Aug 14, 2008
Working on athletics worksheet. Have worked out a formula to attribute a PB (Personal Best) in a MIN range not including the first cell entry (as first entry is not a PB) in events where less =PB
=IF(ISBLANK(F6)," ",IF(F6(MAX(V$5:V6)),"PB"," ")) keeps recognising first entry as PB. Any help greatly appreciated
first entry could be anywhere in the column but all entries after will be below it.
View 9 Replies
View Related
Jan 6, 2010
I'm creating a spreadsheet where a test note taker needs to repeatedly enter the date/time code in a column as events occur.
I tried recording a macro that will enter the =now() formula and then copy that over itself with the value but didn't work.
So now I'm thinking I need to just write code that will determine "now", and write that serial value to the next open cell (with care not to overwrite the previous data point). I'd like to execute this with a simple keystroke.
The thought is to start at the top of the column, go down until the next blank cell is reached, insert the time code (can't be a formula that will change but an actual value).
View 9 Replies
View Related