Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells
Hi all,
Could anybody help me to get the correct code for the following: If F1= K or T or G or Y or Z or x and A1 is Null then A1 = Today(). Regards |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells
There is a problem with your request. You want the formula to be in A1, and
one of the tests you want to perform (A1 is null) is that cell itself. This creates a condition known as a circular referrence error. A (potential) second problem is that once A1 becomes TODAY() it would not be null any longer and the test would never meet the conditions (A1 would not be null) - however the TODAY() would always be whatever today is, not what it was originally. This formula in some cell other than A1 or F1 will give you the result you want, just not where you want it. =IF(AND(OR(A1="",A1=0),OR(F1="K",F1="T",F1="G",F1= "Y",F1="X",F1="Z")),TODAY(),"conditions not met") You might think at this point that you coule put a formula into A1 that just copied the results in this cell and get your date into it, but that will not work either. Say you put that into G1 and put =G1 into A1, then again you get a circular reference error. I think maybe the closest you're going to get (without resorting to VB code in a macro or User Defined Function (UDF)) is this in A1 =IF(OR(F1="K",F1="T",F1="G",F1="Y",F1="X",F1="Z"), TODAY(),"conditions not met") but again, the TODAY() is always going to return the current date when a G, K, T, X, Y or Z is in F1 and it will change from day to day. One possible solution would be to place the following code into the worksheet's code section (right-click on the sheet's name tab and choose [View Code] then cut and paste this into the displayed module) Private Sub Worksheet_Change(ByVal Target As Range) 'this works for all cells in column F 'will put today's date into column A 'on same row when cell in K matches the criteria 'you can change which column to examine 'by changing the "F1" reference in the next line If Target.Column < Range("F1").Column Then Exit Sub ' change not in column K End If 'could have more than one cell as Target if 'you have selected several in the column, 'as to use [Del] to clear or range that 'also includes cells not in column K 'in this case, don't do anything either. If Target.Cells.Count 1 Then Exit Sub ' only work when 1 cell changes End If If IsEmpty(Range("A" & Target.Row)) Then 'nothing in A, so check K for value Select Case UCase(Trim(Target)) Case Is = "G", "K", "T", "X", "Y", "Z" Range("A" & Target.Row) = Now() Case Else 'ignore everything else End Select End If End Sub Two other situations I can imagine you may need would be that if value in Fn changes to or is entered as an 'invalid' character and you need to erase the date in column A, or perhaps only erase date in A if the cell in F is empty - if you need that kind of response, we have to change the code somewhat, to look at Fn entries before examining contents of A. If so, say so and we can work out the code - presuming you decide this code is the way to go. "Pietro" wrote: Hi all, Could anybody help me to get the correct code for the following: If F1= K or T or G or Y or Z or x and A1 is Null then A1 = Today(). Regards |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells
Hi JLatham,
Thank you for your answer.. Actually all that i want to do is to prevent users from changing the date..so i want that once a user of the six ones that i have selects his name from the list in column F,today's date can be updated automaticallyin column A,same time i want to lock and protect the column that contains the dates ,so users may not be able to change it manually,i want also to make the date inupdatable even if a user changes the written name . "JLatham" wrote: There is a problem with your request. You want the formula to be in A1, and one of the tests you want to perform (A1 is null) is that cell itself. This creates a condition known as a circular referrence error. A (potential) second problem is that once A1 becomes TODAY() it would not be null any longer and the test would never meet the conditions (A1 would not be null) - however the TODAY() would always be whatever today is, not what it was originally. This formula in some cell other than A1 or F1 will give you the result you want, just not where you want it. =IF(AND(OR(A1="",A1=0),OR(F1="K",F1="T",F1="G",F1= "Y",F1="X",F1="Z")),TODAY(),"conditions not met") You might think at this point that you coule put a formula into A1 that just copied the results in this cell and get your date into it, but that will not work either. Say you put that into G1 and put =G1 into A1, then again you get a circular reference error. I think maybe the closest you're going to get (without resorting to VB code in a macro or User Defined Function (UDF)) is this in A1 =IF(OR(F1="K",F1="T",F1="G",F1="Y",F1="X",F1="Z"), TODAY(),"conditions not met") but again, the TODAY() is always going to return the current date when a G, K, T, X, Y or Z is in F1 and it will change from day to day. One possible solution would be to place the following code into the worksheet's code section (right-click on the sheet's name tab and choose [View Code] then cut and paste this into the displayed module) Private Sub Worksheet_Change(ByVal Target As Range) 'this works for all cells in column F 'will put today's date into column A 'on same row when cell in K matches the criteria 'you can change which column to examine 'by changing the "F1" reference in the next line If Target.Column < Range("F1").Column Then Exit Sub ' change not in column K End If 'could have more than one cell as Target if 'you have selected several in the column, 'as to use [Del] to clear or range that 'also includes cells not in column K 'in this case, don't do anything either. If Target.Cells.Count 1 Then Exit Sub ' only work when 1 cell changes End If If IsEmpty(Range("A" & Target.Row)) Then 'nothing in A, so check K for value Select Case UCase(Trim(Target)) Case Is = "G", "K", "T", "X", "Y", "Z" Range("A" & Target.Row) = Now() Case Else 'ignore everything else End Select End If End Sub Two other situations I can imagine you may need would be that if value in Fn changes to or is entered as an 'invalid' character and you need to erase the date in column A, or perhaps only erase date in A if the cell in F is empty - if you need that kind of response, we have to change the code somewhat, to look at Fn entries before examining contents of A. If so, say so and we can work out the code - presuming you decide this code is the way to go. "Pietro" wrote: Hi all, Could anybody help me to get the correct code for the following: If F1= K or T or G or Y or Z or x and A1 is Null then A1 = Today(). Regards |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells
Assumptions.............
Column B is currently unlocked. Column A is formatted as Date/Time Worksheet is currently protected with password of "justme" Paste this into the sheet module. Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 6 Then ActiveSheet.Unprotect Password:="justme" n = Target.Row If Target.Value < "" _ And Target.Offset(0, -5).Value = "" Then With Target.Offset(0, -5) .Value = Now .Locked = True End With End If End If enditall: Application.EnableEvents = True ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Gord Dibben MS Excel MVP On Sat, 3 Mar 2007 15:11:15 -0800, Pietro wrote: Hi JLatham, Thank you for your answer.. Actually all that i want to do is to prevent users from changing the date..so i want that once a user of the six ones that i have selects his name from the list in column F,today's date can be updated automaticallyin column A,same time i want to lock and protect the column that contains the dates ,so users may not be able to change it manually,i want also to make the date inupdatable even if a user changes the written name . "JLatham" wrote: There is a problem with your request. You want the formula to be in A1, and one of the tests you want to perform (A1 is null) is that cell itself. This creates a condition known as a circular referrence error. A (potential) second problem is that once A1 becomes TODAY() it would not be null any longer and the test would never meet the conditions (A1 would not be null) - however the TODAY() would always be whatever today is, not what it was originally. This formula in some cell other than A1 or F1 will give you the result you want, just not where you want it. =IF(AND(OR(A1="",A1=0),OR(F1="K",F1="T",F1="G",F1= "Y",F1="X",F1="Z")),TODAY(),"conditions not met") You might think at this point that you coule put a formula into A1 that just copied the results in this cell and get your date into it, but that will not work either. Say you put that into G1 and put =G1 into A1, then again you get a circular reference error. I think maybe the closest you're going to get (without resorting to VB code in a macro or User Defined Function (UDF)) is this in A1 =IF(OR(F1="K",F1="T",F1="G",F1="Y",F1="X",F1="Z"), TODAY(),"conditions not met") but again, the TODAY() is always going to return the current date when a G, K, T, X, Y or Z is in F1 and it will change from day to day. One possible solution would be to place the following code into the worksheet's code section (right-click on the sheet's name tab and choose [View Code] then cut and paste this into the displayed module) Private Sub Worksheet_Change(ByVal Target As Range) 'this works for all cells in column F 'will put today's date into column A 'on same row when cell in K matches the criteria 'you can change which column to examine 'by changing the "F1" reference in the next line If Target.Column < Range("F1").Column Then Exit Sub ' change not in column K End If 'could have more than one cell as Target if 'you have selected several in the column, 'as to use [Del] to clear or range that 'also includes cells not in column K 'in this case, don't do anything either. If Target.Cells.Count 1 Then Exit Sub ' only work when 1 cell changes End If If IsEmpty(Range("A" & Target.Row)) Then 'nothing in A, so check K for value Select Case UCase(Trim(Target)) Case Is = "G", "K", "T", "X", "Y", "Z" Range("A" & Target.Row) = Now() Case Else 'ignore everything else End Select End If End Sub Two other situations I can imagine you may need would be that if value in Fn changes to or is entered as an 'invalid' character and you need to erase the date in column A, or perhaps only erase date in A if the cell in F is empty - if you need that kind of response, we have to change the code somewhat, to look at Fn entries before examining contents of A. If so, say so and we can work out the code - presuming you decide this code is the way to go. "Pietro" wrote: Hi all, Could anybody help me to get the correct code for the following: If F1= K or T or G or Y or Z or x and A1 is Null then A1 = Today(). Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
conditional formating cells i Excel based on other cells values | Excel Worksheet Functions | |||
How to use macros to copy a range of cells which can exclude some cells which I didn't want to be copied? | Excel Worksheet Functions | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
Pasting single cells from Word to multiple cells in Excel | Excel Worksheet Functions |