Locking Cell Dependent On Data Entered
Oct 18, 2009
I'm working in MS Office Excel 2003. I have a spreadsheet with numerous drop down boxes and references to named ranges. Here's my current quandary:
In cell I12, I have a drop down box with listed choices A, B, C and D. In cells I15, I18, I21 and I24, I also have drop downs.
What I'm trying to do is when choice D is picked from I12 it locks cells I15, I18, I21 and I24 so that the choices in the drop down cannot be picked. Also, a popup message is displayed.
I was able to receive help in getting this pretty much taken care of. But the problem I'm having with the code is that the popup message is also being displayed whenever I make a choice from other drop down boxes. So if I choose option D from I12, it locks the other 4 cells and displays the message just fine, but then if I choose anything else from say K12 or L21 then the message continues to come up. Here's what I have...
View 5 Replies
ADVERTISEMENT
Oct 10, 2009
In cell D10, there is a drop down menu with choices A, B, C and D. Cells D13, 16, 19 and 22 also have a drop down menu. What I would like the code to do, is when option D is chosen from D10's menu, cells D13, 16, 19 and 22 are locked so that the user cannot choose an option from any of those cells.
So basically, if D10="OptionD" then [whatever the value is to lock cells D13, 16, 19 and 22 from drop down use)
If possible, I'd like this as an IF function, and not a VBA macro. I'm not completely familiar with all of the IF functions...um...functionalities, so forgive me if I ask the impossible. But if the IF cannot do it, VBA code is fine.
View 9 Replies
View Related
Jan 27, 2013
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?
View 4 Replies
View Related
Sep 22, 2009
I'd like to lock some cells depending on what is put in a particular cell. As shown by the file attached, if the first column (Amendments) has a "no" in A4, I'd like to lock out cells B4:D4. (The sheet will be protected). If it's a "yes" then the cells are unlocked. I'd like to then apply this to all subsequent rows (i.e. lock B5:D5 if a "no" in A5 etc.)
View 5 Replies
View Related
Jun 1, 2013
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.
View 2 Replies
View Related
Jan 4, 2010
I have been assigned the task of creating our new integrated time and leave sheet in Excel 2007, previously we had 2 sheets, one for times and one for leave.
The problem I have is that I need to protect the authorisaton columns therre are 2 one for AM and one for PM, so that only Managers can authorise leave by inputting a password and then initialing the leave.
What I then need to do a the end of each month is for the manager to be able to click a Button with a Macro which will then lock the whole worksheet so that employees cannot change their times after the manager has checked the sheet.
I can lock the authorisation columns and password protect but I have to enable protection on the sheet, when I do this I cannot then figure out a way of running a macro which will then protect the whole sheet and assign a password to it so it cannot be changed after being checked and signed off.
I have tried to create a macro (button) to remove the protection on the 2 columns and then reapply protection to the whole sheet with another button but to no avail.....
If anyone has any suggestions I would be eternaly greatful.
Passwords on the sheet for the different buttons are abc or cobra.
You can download my Spreadsheet here.
[url]
View 9 Replies
View Related
Oct 10, 2008
I recently posted a thread on locking one cell if another had a value in it, and vice versa for the other cell. I got this great response (thanks se1429!) and it works GREAT, but I failed to mention that the worksheet is password protected. It asks for a password when I enter a value in one of the cells. I just need help adjusting this code so I can put my password in the code and allow the worksheet to unlock and lock at will by using this password.
View 9 Replies
View Related
Nov 17, 2008
I want to lock cell after cell in a worksheet after data entry. Entering the data is what drives the locking of the cells. All cells with data are locked but those without are unlocked.
View 9 Replies
View Related
Jan 26, 2007
how can we disable/enable a cell based on a condition which is dependent on other cell data
View 4 Replies
View Related
Jan 27, 2014
I've got a password protected spreadsheet with only certain cells unlocked to allow users to complete them. I need the following to work on Excel versions 2003, 2007 & 2010.
Cell E3 has a drop down list with types of car to choose from. One of the car types is "Pool_Car".
Cell D12 should only be used if "Pool_Car" is selected from the drop down list. If any other car type is selected, and the user tries to enter a figure in D12, I need an error message to appear.
E3 & D12 are unlocked cells.
I'm happy for D12 to become locked & only allow it to be unlocked if "pool_car" is selected, but I've looked elsewhere, but can't find VBA or data validation to work.
View 3 Replies
View Related
Jan 31, 2014
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.
View 2 Replies
View Related
Aug 16, 2012
I need to achieve the situation below with a formula. I have tried simply doing, for example, = B3+1 but this throws an error, probably because the value being checked is alphanumeric.
Data
A001
Data
A002
Add next number (A003) when data entered in cell to left
View 2 Replies
View Related
Jan 5, 2013
Currently have a column of cells that after the data has been entered we would like the cell to lock to prevent changes. The sheet is currently protected with the cells in question unlocked (D173:D250). We would like to be able to change the data if we unprotect the sheet. We would also like to be able to add/delete cells (list) in the code as needed.
View 9 Replies
View Related
May 27, 2014
I have been using Excel for several years and I'm fairly proficient at its basic functionality. I've recently learned and made extensive use of pivot tables. However, I have not taken advantage of such features as Data Validation, VBA, etc.
View 3 Replies
View Related
Jun 10, 2006
I'm creating a "universal-fluctuating" vendor inventory return worksheet for a auto parts store that consist of one criteria (cores, warranties, or N/R ) and will return one or two results of core cost and/or unit cost. This part of the task I have accomplished by using a drop down list for my criteria and my results will appear in two different columns using a Vlookup table. The problem is due to inventory fluctuating from cores and waranties on a month by month basis, vendor requiremnts differ for the number of units returned, and last make the boss happy on ink and papers supplies :D I was wondering if it is possible loop my code in a given column where it will move my code to the next row untill I reach a grand total?
View 8 Replies
View Related
May 20, 2009
I have a Validation drop down box (column B) and a defined range drop down box (column E linked to the Totals tab with A2-A31 defined as Products) that I need to activate everytime data is added to a new line in column 'A' and cells in column 'B' or 'E' are selected. Additionally, they need to activate if the cells in column 'B' or 'E' are selected for any of the old data so changes can be made. This will help reduce the overall file size and calculation time for a year's worth of data.
There is already some code on the worksheet that looks like it can be adapted if someone knows how to code in the drop down boxes.
View 14 Replies
View Related
Aug 17, 2009
Is it possible to have a macro create a hyper link once data is entered into specific cell, i have a multi sheet work book used as a stock card system.
Each stock item has its own worksheet and the product code is entered in to cell E3
(which in turn has a macro in it to change the sheet name to match the product code).
the macro also inserts a row of info into row 5 of another sheet used as a summary sheet ( CONTROL.SHEET or SHEET 4) using copy and paste link.
i need a macro to generate a hyperlink in cell D4 of the control sheet between the row info and the matching product code sheet ( the product code is displayed in cell E4 of the control sheet.
I only input one item at a time and then manually cut and insert the row info into the stock list on control sheet. so every time i input a new item, the row info is always in row 5 of the control sheet.
View 9 Replies
View Related
Oct 2, 2009
I have to keep a record of the running totals of school house points for each week. The problem is that teachers are very lazy and don't record data every week so I have many blank cells which my current formula can't cope with. I've tried using N/A but it doesn’t seem to work? (Have thought about threats of violence but would probably lose my job) I’m if there is no data (blank or 0) then I need it to keep the same total in the cell as the previous week and so on until new data is entered and updates the total. I have attached a simplified copy: Teachers enter points in the HP sheet, the Running Totals sheet (TAB) contains the formula.
View 4 Replies
View Related
Mar 29, 2007
I am trying to unlock a cell if another cell is a certain Value and if it is not that Value to lock again i have got as far as unlocking it but i can not get it to lock if the value is faulse. This is what i have so far!!
Private Sub Worksheet_Change1()
If ("K4") = "Daily" Then
ActiveSheet.Unprotect ("Password")
Range("F6").Locked = False
ActiveSheet.Protect ("Password")
End If
If ("K4") = "Monthly" Then
ActiveSheet.Unprotect ("Password")
Selection.Locked = True
ActiveSheet.Protect ("Password")
End If
End Sub
View 2 Replies
View Related
Jan 10, 2012
I have 2 excel files. One excel file (File A) is a basically a source file on a shared network that users input data into. The second excel file (File B) I created in hopes of basically using to analyze the data in File A. I used a data connection to access the data on File A while on File B. The issue I'm facing now is that when I refresh the connection, it locks File A from being opened until I close File B. Currently, I have the data connection to refresh only manually, so I'll open File B, refresh it, save, close, and then re-open without refreshing, so that way File A only stays locked for that short amount of time. Is there any way to make it such that when I refresh the connection, it doesn't lock File A?
View 1 Replies
View Related
May 14, 2009
I'm creating a spreadsheet for work, which for security reasons requires certain information to be locked except to administrators. However the cells need to be open for inputing information for everyone until the administrator enters specific data.
Completed Date Time W/R No. Acc. No. Priority? Vunerable? Comments Closed Date/Time Yes 14/04/09 11:30:00 1234567890 8890976 Yes Yes N/A 14/04/2009 11:30
What I am looking to happen is once an adminstrator selects 'Yes' from the drop down menu on the Completed column, for that row of data to be locked for editing, unless unlocked later by an adminstrator.
View 9 Replies
View Related
Apr 18, 2014
I am working on an Inventory Spreadsheet. I have need it to date time stamp that willpopulate a different cell /field when text are entered in a different cell/field and will not change all the previous dates entered on the spreadsheetevery time I re-open the spreadsheet. I formula I am using is =IF(E3<>"",IF(D15="",NOW(),D15),"")and it changes each time I open thesheet to do the inventory so I do not know the date of the last inventory. Iwant to capture the actual date that the inventory was completed in thesecond/different cell?
View 9 Replies
View Related
Jul 17, 2014
Is there a way in Excel (2010) to lock data from being entered into cells based on the value in another cell? Here is an example:
Row 4 contains dates from 2015-2030 as a header starting at column C (C4 and on) that represents years of revenue. Column B contains dates as well, this date can be any year but this becomes the driver for the data input in columns C through X. Column B contains the "Delivery Date"
Data is input in Columns C and so forth. The issue becomes that let's say that the date in C4 is 2016 and this is the beginning year. Obviously there should be no inputs for 2015 (C5) and the data should start to be input for this year at 2016 (C6). Often people just begin filling in the first available year not looking at what the year actually is.
Is there a way to add a formula or some logic/protection to prevent inputs in previous rows based on the values in column B? So if the value in column B is XXXX than there can be no inputs in cells less than that value?
View 6 Replies
View Related
Mar 8, 2013
Is it possible to have a formula in a cell, based on the value in another cell, but if the other cell value is not true then allow for data entry in the cell
So
J10 executes a formula if the value in I10 (a list field) is 'Cash and Shares' =IF(I10="Cash & Share",ROUND(IF(AND(K10<>0,O10>0),K10/O10,0),0)).
However if the value of I10 is anything other than 'Cash and Shares' then do not execute the formula in J10 but allow users to enter another value.
Note: the cell needs to be locked. The worksheet will be protected and will allow users to Select Locked Cells and Unlocked Cells.
View 3 Replies
View Related
Mar 30, 2012
I am working in a workbook that uses hyper-links to navigate between locations on various sheets.
When rows are added to a sheet above the destination cell, the hyper-link no longer points to the original data but has moves the corresponding number of rows that have been added.
Is there anyway the hyper-link can be locked to a cell and adjust automatically if new rows are added or existing rows deleted?
View 3 Replies
View Related
Jan 22, 2010
Situation:
I have two drop down lists 1) Country and 2) States/province
Country has list: [US, Canada]
If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list
If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list
Scenario:
First, I select "US" and choose the states to "Florida"
Next I change the country to "Canada" and forgot to choose province
Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec],
BUT the current value is still "Florida"
Probem:
Now I have "Canada" and "Florida" selected in the sheet
Question:
If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?
View 11 Replies
View Related
Jul 16, 2013
I have an excel sheet which will be password protected and only opened as Read-Only. There are 3 cells which have a dropdown list associated to select YES or leave blank. When YES is selcted in any of the 3 cells i want to lock the other 2 cells.
View 9 Replies
View Related
May 7, 2014
When cross linking cells from one file to another, I sometimes lose the source link to rearranging cells on one of the other spreadsheets. Is there a way I can "lock" the source cell so that it isn't lost as a reference?
View 1 Replies
View Related
Feb 26, 2007
In Excel 2007 I protect cells in certain cloumns in a large amount worksheets. By manually protecting or locking them. Once the sheets are protected without a password just blank and only select unlocked cells is ticked for sheet protection. I close the workbook and reopen it. These cells are now unprotected that were protected and theres always two ticks checked off for select locked cells and select unlocked cells.
View 9 Replies
View Related
Dec 8, 2008
how to lock ALL cell references in the formulas within a range in one go, rather than ahving to go to each and every cell and lock them manually?
I have a table with 966 cells all with just one cell reference like this: .....
View 9 Replies
View Related
