Automatically Update A Protected Cell Based On Contents Of An Unprotected Cell
Apr 30, 2009
I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.
For example:
Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.
If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
View 5 Replies
ADVERTISEMENT
Apr 26, 2013
I am working on a manpower spreadsheet and need for one spreadsheet to extract data from another and automatically update if it is changed and I am struggling below is an example of what is on spreadsheet 1:
Mentor.jpg
and the information from spreadsheet 1 is copied to spreadsheet 2, however if amendments are made to spreadsheet 1 they are automatically updated on spreadsheet 2:
Spreadsheet 2:
C0-ord.jpg
View 1 Replies
View Related
May 19, 2010
Using excel 2003, Workbook has several sheets, all sheets are protected workbook is not. I have my cells containing formulas locked, I have unlocked the other cells that require input of various data. When the user goes to type in a "unlocked" cell, (to over write the now obsolete data) it will not allow the user to delete the data, the curser remains a cross and the formula bar or the cell itself will now allow any changes.
I have double checked that the cells are unlocked. I can enter data after the last entry in the unlocked row and or column, but I want to be able to "cut or delete the data that is no longer need and begin entering data from the beginning cell of choice. I want to copy this "old" data to an archive sheet, but it will now allow me to select it.
Example: I have 6 columns starting, (a to f) the formulas are in columns e and f and start at row 4 These columns are locked and hidden. Columns a,b,c and d are unlocked for user input. All data is started from A5 which I want it to start from each time the old data is deleted and new data entered. If I place my curser on any of the cells in the e and f rows, the cursor remains a cross which is what I expect it to do, but columns a to d are doing the same thing, they are acting as if they are locked OR atleast those cells in the a to d areas which have existing data, as I can enter below.
View 7 Replies
View Related
Jan 28, 2013
I would like to automatically color the fill of a cell based on the cell contents.
For example, if the cell content is "1" I would like the color of the cell to be cyan and if the cell content is "2" I would like the color the cell to be red.
I've tinkered with several different formulas but I just can't get anything to work.'
View 2 Replies
View Related
Oct 18, 2013
Let's say that in column A I have numbers,"Yes" and "No". I want in column B to have only the numbers from column A, in the same order without any empty ranges, and everytime I add in column A a new number, column B to update automatically with that number. Let's have an example:
A B
Yes 12
12 13
No 10
13
No
10
Yes
And if I want to add in column A:
A B
Yes 12
12 13
No 10
13 25
No 15
10
Yes
25
15
So the column be will update automatically. I already tried =IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROWS(B$1:B1))-ROW($A$1)+1),") but using this many times get's my file very heavy and the excel is working slow.
View 13 Replies
View Related
Apr 17, 2013
S/W Environment: Excel 2010
Some of my worksheets are permission protected. They will allow me only to view the sheets - No editing, copy, cut, sort, add filter, etc. Is there a way to hack this and copy data (including formating) into other / new worksheets?
View 1 Replies
View Related
Jan 28, 2008
The Special GoTo function doesn't have a listing for highlighting (going to) only protected or (preferribly) unprotected cells. Is there an easy way to do this?
View 9 Replies
View Related
Feb 24, 2009
Excel 2007 or 2003 Is there code to be able choose the formating of a cell when right clicked if the cell is Unprotected. But, the sheet is Protect and the Workbook is Protected. Without Unprotecting anything 1st.
The reason being is sometimes the user may need to change the type of font from Arial, or Gdt or Y14.5M.
View 9 Replies
View Related
Feb 22, 2012
Cell C3 has "Joe"
Cell X44 is a VLOOKUP that retrieves "Joe"
Let's say Joe goes on vacation. The workbook user goes in and puts a blue fill in C3. Cell X44 would also need to change automatically.
What's needed to make this happen?
View 1 Replies
View Related
Mar 6, 2012
I have a file used by numerous facilties. I would like code to track any time a protected sheet has been unprotected / protected. I would also like the stamp to show the user data if that can be done.
View 2 Replies
View Related
Apr 9, 2014
Is it possible to adjust column width of a PW protected worksheet without unprotecting it, or unprotect it and adjust in background then PW protect it?
On sheet1 H1 = column width value
Will adjust Sheet2 (password protected) column E width
View 9 Replies
View Related
Dec 11, 2008
I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'!
D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?
View 5 Replies
View Related
Mar 12, 2014
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies
View Related
Jan 2, 2010
I am trying to create a simple scoring system for sports that use a "legs" and "sets" format (e.g. tennis, darts etc.)
I would like the "sets" cell to automatically update as the "legs" cell reaches the required amount of legs to win a set. After that I would like the "sets" cell to continue to count upwards when/if another set is won. Is this possible?
View 9 Replies
View Related
Dec 20, 2013
Lets say I have two sheets titled "dashboard" and "raw data" in a workbook. Cell A1 in "dashboard" should always show the most up to date data from Row 1 in "Raw Data".
I update "raw data" daily, adding a new column to Row 1. For example December 19 would be A1, December 20 would be B1, December 21 would be C1 etc...How can cell A1 in "Dashboard" always reference the newest cell in Row 1 of "raw data"?
View 2 Replies
View Related
Mar 1, 2008
Is is possible to have Excel update time automatically in a given cell within the spreadheet.
I have a spreadsheet that I need Excel to update the time automatically .
I would like Excel to update the time automatically in cell A1.
I have done some research and found the following thread but this only works when we close and open the spreadsheet.....
View 9 Replies
View Related
May 31, 2006
I have a worksheet(A) with rows of data associated to a date that gets a new row of data points once a month. On the second worksheet(B) I have two columns, one (Current Month) to show the data in the newest row (transposed), and one (Previous Month) to show the data in the row above it (from the previous month)(also transposed). Is there any way to get the columns in worksheet B to automatically read the newest row of data from worksheet A into column Current Month and automatically update the column Previous Month with the row above it?
View 7 Replies
View Related
Oct 13, 2009
Is there way to automatically clear the contents of a cell when the user clicks in that cell. Eg, click in "A1" then then "A1" clears automatically.
View 10 Replies
View Related
Jan 17, 2013
Is is possible to clear the contents of a cell range if a message box appears stating an error. I have tried the following code but the logic doesn't actually work and am struggling to achieve what I am trying to do.
My code so far is as follows:
Code:
If ActiveSheet.Range("F84") > 0 And ThisWorkbook.Worksheets("PES").Range("D24") = 0 Then _
MsgBox "Your Entitlement is currently 0", vbCritical, "Error"
ActiveSheet.Range("K84:T84").ClearContents
The message box appears fine if the IF statement is true but if IF statement is false, the cell range of K84 to T84 on the current sheet is still cleared..
View 2 Replies
View Related
Jul 6, 2009
Is it possible to export Excel cell contents to Word fields in a protected Word document? For example...
What code would be needed to tell Excel to open up, copy and export the contents of A2 in the active sheet of a workbook to "Field 2" in a Word document named "Report 01" and then put the contents of B2 to "Field 2" etc?
Do both applications have to be opened up at the same time or is Excel able to open up Word on its own? Will the macro be able to....
1. Automatically open up the correct Word document?
2. Look ONLY in a certain folder for the "Report 01" Word document?
or
Bring up a "selection" box that allows you to select the document you wish Excel to export it's data to?
3. Close and save the Word Document without any user intervention?
View 2 Replies
View Related
Dec 20, 2012
I am trying to create a job data sheet as per below. Under 'Sales Item' I have a drop down menu to select the item sold. I am trying to set up an autofill formula to fill the 'Stock Cost' cell once we have selected the relevant product from the drop down menu. The data is on another worksheet on another tab at the bottom of the sheet.
Sales Item
Sale Price
Stock Cost
Profit Per Item
Installation Commission
£0.00
£0.00
£0.00
£0.00
[Code] .....
View 2 Replies
View Related
May 3, 2006
I am linking to cells on a spreadsheet and in some cases the formula is showing in the cell and in others the contents of the linked cell. I have forced updating (F9 )and F2 enter with no effect. Is their something i am missing as I want all the links to update automatically. If I copy and paste a working formula into one of these cells it updates albeit with the wrong reference but if i go in and edit F2 etc then it shows as a formula.
View 4 Replies
View Related
Oct 15, 2008
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View 5 Replies
View Related
Feb 18, 2009
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies
View Related
Apr 28, 2014
I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.
I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?
In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.
View 5 Replies
View Related
Feb 13, 2014
I'm migrating data from Financisto for Android to CashTrails for iOS, and I need to format my CSV file a specific way for conversion to a CashTrails file.
I'd like to do the following in Excel 2013 (I have access to other versions if I need to):
1. Highlight a range of cells (i.e. A1-H20)
2. Apply following action: IF cell has data, add " to beginning of cell and " to end of cell, AND IF cell has no data, place "" in cell.
This can be done, right? I'm desperate!
It would look like this in Notepad.
Before:
Date,Time,Amount,Category,Tags,Account
12/31/13,12:00 PM,$3.99,General,,Checking
12/31/13,12:30 PM,$5.00,Shopping,gift,Cash
After:
"Date","Time","Amount","Category","Tags","Account"
"12/31/13","12:00 PM","$34.99","General","","Checking"
"12/31/13","12:30 PM","$5.00","Shopping","gift","Cash"
View 2 Replies
View Related
Sep 12, 2013
How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies
View Related
Oct 20, 2013
I'm trying to set up a dependent cell (B) based on what is in the independent cell (A)
If A is blank, B should be blank
If A has any value other than X, B should display a set string Y
If A contains X, B should contain a drop down list with a range of choices.
I've tried setting it up as a dependent validation cell and I've tried using OFFSET, but the best I can do is to get three different drop down lists to populate cell B. One with only string Y, one with only a blank, and one with the range of choices. The problem is in the first two cases above, I want the cell to fill automatically with either blank or string Y -- I don't want them to just be drop down options, with the cell displaying the last value that was input.
View 2 Replies
View Related
May 2, 2012
I am creating a macro to automate data analysis for work, but I've become stuck.
I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.
View 1 Replies
View Related
Mar 9, 2013
I am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.
I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..
View 9 Replies
View Related