Preventing Cell Reference Deletion
Nov 10, 2008
I have a workbook that contains 2 worksheets. The first worksheet contains all the data. The second sheet contains formulas that reference (absolute for all) the first sheet.
I run into problems (actually the users do) when the data set is updated with new information. Sometimes the users will delete all the data rows first and then copy the new data to the blank cells. When they do this, it messes up the formulas with #REF! error. Is there a way to prevent this? or is there a way to hard code the formulas so I don't get this error?
View 4 Replies
ADVERTISEMENT
Apr 11, 2007
How can I protect individual cells in a range from being deleted when I want to delete data from the surrounding cells??
View 3 Replies
View Related
May 8, 2007
I am trying to make a userform in my excel document that will allow the user to select (perhaps from a combo box) and delete a sheet they want. More sheets will be added and so the combo box or whatever should be able to reflect this. It should prevent them from deleting sheets I specify in the code as well. I've had a quick go doing this myself but I decided to start again as I had soo many problems. I think I just did it in a very long-winded fashion, so I would be interested to see what people come up with.
View 2 Replies
View Related
Aug 4, 2014
So I have this table... At the bottom of the table the Outer Fences. There is a column at the end of the table that calculates if the value in the row is outside the fences. I tried to do this by using the cell (say, H1500) in the if statement, but as I continued down the column I noticed that row 2 used H1500, row 3 used H1501, row 4 used H1502, and so on. So I have been just entering the new fence numbers as the data changes. Is there a way to go into the if statement and somehow "lock" the cell value so it doesn't change?
View 2 Replies
View Related
Oct 2, 2013
How do I prevent or allow text to be entered into a Cell or not. Ie if cell A1 contains the word "Text" I want text to be able to be entered in A2, but if A1 contains "No Text" if want to prevent text being entered A2. Is this possible, if so how?
View 1 Replies
View Related
Nov 12, 2008
if column E is "Annuity", you can type a value in column J and it runs a formula to calculate a value for column K. If E is not "Annuity" it clears the contents of J (which should clear K). What's not working is when column J gets cleared or manually deleted, column K must be blank. The worksheet_change event is not working for deletion of column J.
View 5 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
Feb 25, 2007
I have a cell for example with:
"
11111
22222
44444
77777
"
What the effective way to delete all empty rows in current cell? I confused with VbCrLf, VbNewLine and e.t.c I want to get after that:
"
11111
22222
44444
77777
"
View 4 Replies
View Related
Sep 24, 2009
On some of my worksheets, the slider bar on the right has become a small square rather than a long rectangle. This causes this slightest shift downward to jump through large amounts of rows. Excel seems to think there must be data down there I need. How do I get rid of empty cells at the bottom of a worksheet so that this no longer happens?
View 2 Replies
View Related
Oct 6, 2008
is it possible to delete/remove all non use cells
for example if i only want the cells A1 THROUGH TO O31
all the rest removed??
View 5 Replies
View Related
Jun 13, 2013
I have completed an automated time sheet but have realised that there are some days where overtime entries make it go wrong.
There is one column where you enter an overtime shift when the cell has an entry I need all the other calculations to be zero
A1 START TIME
B1 END TIME
C1 HOURS WORKED ARE CALCULATED using a formula
D1 OVERTIME COLUMN ( IF AN ENTRY IS PUT IN HERE MANUALLY I WANT C1 ALWAYS BE 0
I tried various ways with isblank but just get true or false.
View 2 Replies
View Related
Feb 6, 2007
This time ive created a visual basic form that asks for a row number and when you put a row number in this form it deletes what is in column A, B and C of that row and shift the rows up so that there are no gaps between the data in the rows.
I need to know what code i would have on the 'delete' button of the form.
If you want me to describe it any further i would be happy to, just ask.
View 9 Replies
View Related
Mar 21, 2007
Im looking for code for a button that asks for a Record number, and it will delete the record. But the record number is written in cells as 'Record No.1' 'Record No.2' etc. But i want the messagebox to ask for a single number and it would delete that record. Now the position of the records is what will be the difficult bit about this code, but it has a pattern.
For Record No.1 i need deleting cells C2:J100
For Record No.2 i need deleting cells L2:S100
For Record No.3 i need deleting cells U2:AB100
If anyone could give me a bit of code that works for these 3 then i could waste a load of time filling the rest of them out.
View 2 Replies
View Related
Nov 13, 2008
I have a code where column K is not responding to a value being deleted out of column J. I've highlighted the portion of the code dealing with this. Deletion should trigger the worksheet_change event to clear out column K as it's supposed to. how to get deletion to take effect?
View 4 Replies
View Related
Nov 14, 2008
This is part of a macro in a worksheet_change event. When a cell in column J gets deleted by a user, the corresponding cell in column K should also clear. But it's not responding to the delete.
It DOES clear when the other 2 criteria are met (.cells(1,10) = 0 and .cells(i,5) <> "Annuity"). The worksheet_change event should pick up on the cell deletion, but it's not. And column J is already a trigger for the macro to run, so I'm not sure what's going on. Either the trigger is still wrong, the isempty(.cells(i,10)) is not correct syntax, or this event just doesn't respond to cell deletion.
View 6 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
Mar 2, 2009
I'm using an IF formula – BV2 =IF((OR(BQ2="yes",BU2="yes")),"Yes","No") - which just shows "#VALUE!" whenever BU2 shows "#VALUE!". Is there any way round this so that, if BQ2 showed yes and BU2 showed value, BV2 would say yes?
View 9 Replies
View Related
Jan 8, 2012
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
View 4 Replies
View Related
Mar 11, 2009
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:
View 2 Replies
View Related
Feb 15, 2010
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
View 14 Replies
View Related
Jul 20, 2014
I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:
Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say
AAB
ABC
CDE
And have a formula in column B that converts this to
=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)
I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?
View 2 Replies
View Related
Apr 25, 2014
Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.
View 9 Replies
View Related
Jun 13, 2008
I'm working on a employee evaluation form. I'm using a Pass/Fail system.
I have a cell designated for PASS & one for FAIL. In these cells, I would put
"YES" if they passed, "YES" if they failed that section.
How can I prevent someone from mistakingly entering Yes in both cells?
View 9 Replies
View Related
Dec 11, 2012
I've generated a sheet where, based on the total run hours of a generator set using a given fuel type, we can determine which engine to run on what fuel when. The problem I've run into is that if more than one engine has the same runtime, the chart returns just the lowest numbered engine and eliminates the remaining engine(s). What I'm trying to do is make it so that when this occurs, the suggested engines show up in numerical order. For example instead of 1, 1, and 1 showing up on the chart, something like 1, 2, and 5 will show when they all have 0 run hours. I've attached my chart below with some engines having the same runtimes. RunOrder.xlsx
View 5 Replies
View Related
Oct 26, 2009
Is it possible to prevent the VBA window from popping up if an error occurs when running a macro and the user then selects debug? I've locked the code for reading but I was still able to change the code and save it!
Basically, I want the code to be unchangeable unless done through a known password. What I don't want is some user who may accidently stumble upon the code and change it unwittingly (or voluntary).
View 5 Replies
View Related
Dec 4, 2009
Is it possible to prevent a user from being able to move a userform around the screen area (clicking and holding caption bar)? I am captureing a image and using Pixels coordinates on the capture and need the userform to stay in the center of the screen or the image will not be catptured. The StartupPosition is center of screen, tried
View 2 Replies
View Related
Jun 5, 2008
I am using the following COUNTIF formula which I am sure is supposed to prevent '#N/A' from appearing (According to [url]
View 12 Replies
View Related
Mar 18, 2008
I have made an integrated timesheet for everyone at work. Problem is, people will be funny-beggers, and want to password protect their workbooks.
I want to be able to prevent EVERYONE from creating passwords to protect their workbook.
View 9 Replies
View Related
Jun 5, 2008
I would like to prevent the user from inserting rows. I know that ater versions of Excel have built in "Protection" features which would easily accomplish this. Unfortunately, our company uses Excel 2000.
I have keyed in a constant value in cell B100 ("bottom" of the practical range). I have tried ...
Private Sub Worksheet _Change (ByVal Target As Excel.Range)
MsgBox "Range" & Target.Address & "Was Changed."
If Range ("B100").Value = "" Then
Application.Undo
MsgBox ("Do Not Insert or Delete Rows")
EndIf
End Sub.
The problem with the above is: The "Change" macro does not seem to detect the insertion of rows. It does, however, detect the deletion of rows.
View 9 Replies
View Related
Jan 22, 2009
I have an Excel 2007 file that is a work of art. Users manually copy several rows and columns from a table on a web page and paste it into my Excel file. This turns my beautiful Rembrandt into a Jackson Pollack mess. The formating of my cells is replaced with the formating from the web page. Is it possible to make the Excel file ignore the formatting of the data being copied? As a default?
View 9 Replies
View Related
