Check If Formula Deleted
Jun 6, 2007
I have a spreadsheet that has a formula placed from cells B21 through to AT21. This formula is present in the sheet 95% of the time, but occassionally the user has to enter his/her own value in one of the cells.
How can I check or the user be reminded that he/she has deleted a formula and replaced it with a user defined value. ie a message box(only as a warning) to be displayed just before the workbook is printed. The user should be able to acknowledge the message displayed and carry on.
View 9 Replies
ADVERTISEMENT
Feb 19, 2014
I'm not sure if this is impossible (surely not) or if I'm just Googling the wrong terms (most likely). I have a sheet that we use to record staff shifts and when they take their lunch breaks. By default most of our employees have a 1 hour lunch and they take it 4 hours after they start. So far so good, I've set up a formula to work out what time their lunch would start and end based on this.
N237: =IF(AND(L237>0,L237<>"NWD"),SUM(L237+(1/6)),0) - Lunch start 4 hours after start time in L237 (NWD = Non working day)
M237: =IF(N237>0,SUM(N237+(1/24)),0) - Lunch ends 1 hour after lunch start time in N237
However, not everybody follows the default - some people take lunch later, or don't take as long due to working shorter hours.
What I'd like to do is allow users of the sheet to overtype the times generated by the formulas when applicable, but if they delete their own custom value for lunch breaks I would like the formulas to re-appear and thereby return lunch times to the default values.
I could do this through VBA with a button to look for blank cells, but I would prefer it to happen automatically. Is this possible and if so what code would I need to make the magic happen?
I should maybe have mentioned that this sheet has space for 250 names and an area for each day of the week, so these formulas would appear in 1,250 different places
View 4 Replies
View Related
Apr 20, 2013
I am using following code to effect a change which works fine:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Closed Flts").Unprotect "abcd"
Sheets("Faults").Unprotect "abcd"
[Code].....
But when the sheet is unprotected to effect this change the formula in cells ( row I and L) are visible to user and can delete them. Or they can even delete the cell itself or modify formula.
The formule in Row I is
Code:
=IF(A12="","",IF(G12=1,A12,IF(G12=2,A12,IF(G12=3,A12+1,IF(G12=4,A12+5,IF(G12=5,A12+28,"priority?"))))))
And in Row L is
Code:
=IF(A13="","",IF(I13
View 1 Replies
View Related
Mar 25, 2012
I would like to use the code below such that column "A" contains unit list pricing, Column "B" calculates the discount from the net pricing in Column "C." For example, A1 = 100, B1 = 1- (C1/A1), and C1 by default is equal to A1 but if the user enters in a net rate, say 85 in C1, it retains the 85 in C1 but if then later deleted, the C1 automatically reverts back to =A1. The code below works great for C1 but for the life of me, I cannot figure out how to modify the code and have it work correctly for a range of cells in Column "C" or all of Column "C."
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "C1" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "=A1"
Application.EnableEvents = True
End If
End If
End With
End Sub
View 2 Replies
View Related
Jun 27, 2013
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
View 8 Replies
View Related
Nov 30, 2009
I need two formulas that can do the folowing:
• check whether a cell contains text, if so, then trim all spaces and get result. If it doesn't contain text then set the result as blank.
• check whether THE CELL T13 contains text, if it does then check the adjected cell (U13) if it contains text, if so, then concatenate the two cells with a space between them and remove any other spaces. If the T13 doesn't contain text, the set the result as blank. Also, I have another question: how could I use the counta formula without counting spaces?
View 2 Replies
View Related
Jul 3, 2007
=IF(OR(Q136<O132,Q136>O132),Q136-O132,O132-Q136)
This does not seem to be working for me. I need to check one number against another and if the one cell is larger then the other subtract it or just the reverse.
View 12 Replies
View Related
Dec 16, 2008
I need a solution for this situtaion! Iam using a french version of excel2003!
I have #DIV/0! in P24 (i.e an error value) So I need to check in Q24 such that if (P24=#DIV/0!,0,P24). I need to have a logical formula for this one!
View 3 Replies
View Related
Jul 16, 2009
I have a direct mail company and lock in certain areas for a certain amount of time for my clients.
I want to be able to check a hand full of zip codes to see if they are aviable for new clients.
The lock in period is 60 days.
For a zipcode to be available it can not be in my list or has passed the 60 day expiration.
Attached file.
View 9 Replies
View Related
Feb 12, 2014
I need to come up with a formula to verify that the last 3 characters of a text string match the 3 characters in the adjacent cell. So I am comparing in the example below Column B to Column A.
Ex:
A1 010 B1 3.010
A2 010 B2 1A.010
My spreadsheet is very large and I need to get this done without manually having to look at each.
View 2 Replies
View Related
Nov 23, 2006
I have a Formula in Cell A1 and I am entering a Vlookup in D1, I get a #N/A in D1. If I enter for example Monday in Cell A1 instead of the formula, Vlookup works. My question is, will it not work in a Cell with a formula?
View 9 Replies
View Related
Feb 19, 2008
I have a worksheet I need help writing a formula for. I need to be able to check which recruiter (A711:A718) has the highest amount of contracts (B711:B718). I can write a formula for that, but this year each recruiter must have at least 30 contracts (B711:B718) and 90 plans (B711:B718). So in summary, I need to place in one cell the recruiter name that has the highest amount of contracts as long as they are above 30 contracts, and 90 plans. In the next cell I will need to do the same for the recruiter in 2nd Place.
View 9 Replies
View Related
Apr 23, 2006
is there a way to check for a value in a range, and return the value if it is not in the range, else return nothing?
H1 =85
B | C | D
25 | 50 | 75
40 | 30 | 10
12 | 9 | 66
None of the following IF statements will work:
{=If($B2:$C4="","",If(B$2:C$4<>H$1,H$1,"")}
{=If($B2:$C4="","",If(B$2:C$4=H$1,"",H$1)}
View 2 Replies
View Related
Oct 19, 2006
I got a pivot table that when selected will give me a value (actually its an ID number) I want in H3. I then got a list of values outside of the pivot table in column J. If H3 matched any of the values in column J, I need a “Warning”, if not “OK”. I have been using the formula =IF(H3=J3,"OK","WARNING") to match selected cells (H3 with J3) but cant get it to work to look down the column list. The OK or Warning is in cell F34 out of arms way!
View 2 Replies
View Related
Feb 15, 2007
I have two cells which refer to employee number: A1 and A2
(employee numbers are 001, 002, 003, etc)
I want Cell A3 to check if either A1 or A2 = 001
If both cells is 001, I want an "error message" because that is not allowed, and if one of them = 001 then I want the value from Cell A4 to be but in Cell A3.
So in my head the forumla should go like:
=If(A1 Or A2 = 001;A4;"-";)
View 9 Replies
View Related
Dec 16, 2013
I have a tab with about 10,000 records with IDs and timestamps (date and time). On another tab I have a list of IDs and start and end timestamps. There are duplicate IDs on both tabs. I want a formula for the 10,000 records tab that checks the ID and timestamp for each row against all the IDs and start/end time stamps of the other column and flags the rows where the ID and timestamp fall within the start/end timestamp on the other tab. It seems pretty simple, and it feels like the code should be something like this (but this code doesn't work):
=IF(AND(F2=List!C:C,B2>=List!F:F,B2<=List!G:G),"Y","N")
where F2 is the ID and B2 is the timestamp on the records tab. The "List" tab has the ID in column C and the start/end timestamps in columns F and G respectively.
View 1 Replies
View Related
May 5, 2009
What would be the correct formula to check the format of an address that must start with a number followed by a space then text - in cell B4
At the moment I have:
=IF(B4="","r",IF(ISERROR(SEARCH(" ",B4,1)),"r",IF(ISNUMBER(VALUE(LEFT(C5,SEARCH(" ",C5,1)-1))),"a","r")))
If it is incorrect or missing an 'r' should be displayed, an 'a' if correct
View 14 Replies
View Related
Nov 9, 2013
i wanna to put check boxes in colums d4:d41 with a formula inside which beginning with this formula (=Sheet1!$D$4) in cell d4.how to copy and drag down this formula into d5:d41?
View 8 Replies
View Related
Jul 24, 2006
Is there a formula I can use to see if a cell is highlighted? I need to filter for highlighted cells and want to use a formula to produce a binary result (1 for highlighted or 0 for no highlighting) so I can filter on that to only the highlighted cells.
View 5 Replies
View Related
Dec 21, 2011
I've two lists of text - one long with multiple words and one short with one or two words. (sample data: [URL]
I would like to check if List A has the word(s) present in List B, and return a Y/N accordingly.
View 9 Replies
View Related
Jan 9, 2014
I have a worksheet which J3:NJ3 lists the dates for the calendar year. (Example - J3 = 1/1 , K3 = 1/2 , etc. )
A3:C3 are my headers for project information (project name, manager,etc)
In D3 I will be entering employee names. in E3 I will be entering Project start dates, and in H3 will be end dates. These dates are selected via validation lists of the dates listed in J3:NJ3, and it is set to auto fill the adjacent date range with an "X" after the start and end dates have been selected.
Now I also have another worksheet which is a pivot table that will auto update upon any workbook change, and will show each employee and all of the dates in which they are scheduled to work. I am using conditional formatting on this sheet to highlight any dates that the selected employee is double booked on two or more projects.
I want to keep this second worksheet as is, but to avoid having to flip flop back and forth between tabs to find out of someone is double booked, I would like to add a column (I) which will either say "Available" or "Double Booked" . Is there a formula that I can use for this? I thought about Vlookup, but I don't believe I know of a way to write a Vlookup formula that can check a dynamic range of entered dates. I thought perhaps a nested Vlookup/Hlookup, but not quite sure if that will work either.
View 7 Replies
View Related
Aug 12, 2008
I have the following data (as below).
Basically I want to check if a value is in AC but not AD, I would like to mark it as such in column AE i.e. mark all datevalues in column AD that aren't in column AC.
Could anyone please help with the required formula?.....
View 9 Replies
View Related
May 9, 2006
I have software that generates an excel worksheet with data with dates. In this column of dates, there are either valid dates or dates with all zero's (00-00-00). I need to setup a formula that checks this column, if they have a valid date (anything greater than 00-00-00) then mark that row with "Contract", if hit has all zero's (00-00-00) then mark it as "CP". One thing, this column is not formatted as "Date", it is formatted as general. I didn't know if that made a difference.
Also, is there a way to setup this WHOLE column with this formula so that as soon as the data is extracted, it will automatically change the cell value to CP or Contract?
View 4 Replies
View Related
May 19, 2008
I want to catch if the user has deleted a row. There is a need to know when a row is deleted and what the data was in that row. So, what I'm trying to do if a row is deleted is to undo the action and then change the font of all data in that row to have the strikethrough effect. This will enable others looking at the workbook to know that the information in the row existed but is no longer relevent.
So, the code is something like this:If row deleted Then
Application.Undo
Target.Font.Strikethrough = True
End If
View 9 Replies
View Related
Apr 7, 2014
I am looking for a single formula I can run along a single row that looks at several columns of data, and then checks if the date range falls between the running row, take it's relevant number, and multiply it by a single rate. The attached probably explains it clearly - I think the solution would be some sort of Sumproduct, but my attempts have failed!Example - Date Range with Calculation.xlsx
View 10 Replies
View Related
Aug 22, 2008
Does excel have a formula to check to see if every row in a specific column is blank and if it is then set the value?
View 10 Replies
View Related
Oct 12, 2006
Is it possible to have a formula check the color index of a cell?
Example if A1 color index = 6 then X
View 9 Replies
View Related
Jan 5, 2009
Ok. I am creating item lists in excel for every product that my company sells. I am in need of a formula that does the following:
I want the cell to check to see if there is a value in another cell, and if there is anything at all, i want to display the word Yes as the value, or leave the cell empty.
So I want the cells in Column A to Check if the adjacent cells in Column B have a value. If the column b cells have a value I want the cells in Column A to display the word "Inventory Part". If there is no value I want the cell left blank.
View 7 Replies
View Related
May 7, 2012
Can I use conditional formatting to check if a field has a value or a formula?
View 4 Replies
View Related
May 14, 2012
I'm entering some time data (in format hh:mm) into Columns. Pretty basic Workbook that I have been given and, to be honest, it's just donkey work putting the data in. The times going down the Columns need to be progressive (ie B8 is arrival time, B9 is admission time etc). This goes through to B27 and then repeats from C8-C27 and ultimately J8-J27.
I'm looking for something as a formula to pop into Column K (or VBA, I don't mind!) which will do a simple verification to ensure that the times I enter is not less than the any of the cells above in the aforementioned ranges. I can do simply the cell above, but due to rusting of brain, can't think how to do this for the range!
View 9 Replies
View Related
