• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel 2007 Nested IF Formula

Hi,
We have part of it correct by transfering the on the Timeline tab, however it is not transfering in the respective column to show the status of the file.
eg. the first row if I change the status from 1st Ack. FI requested to Additional FI received, and I change this date to the 19/4/2016, I would like this change to show up under the respective column in the Timeline sheet which is column F for this status change ' Additional FI received" so the date 19/4/2016 will be there. And so on for the rest of options in the dropdown menu.
 
oh waw looks great! However i just got a runtime error about date mismatch? not sure if i mistyped something....but its gone now Also can the timeline tab start to populate as soon as i put in the date of the application being received on the CEC tab?
 
Also how did you do this? Would there be some formula or sheet that I need to protect so that no one can change it? I need to get you a present for helping me so much :)
 
you sir are very awesome. PERFECT. Thank you! Everything seems to work fine. If I encounter any issues i'll let you know. Thank you again! I am so happy haha:DD:DD:DD:DD:DD:DD:DD:DD
 
Hello,
So after trying some calculations on the worksheet there are a few issues arising.
1. I'm getting an error for the VBA if the date of receipt and and date of status change is the same. eg if the file status is application received, both the receipt date and the change of status date will be the same but that will only happen for that one option of application received. Then the option comes up to debug and the VBA opens and I don't want others to access the VBA to mess it up. This is what comes up below.
upload_2016-6-10_9-53-29.png

2. Can the timeline sheet start populating from the time I input the CEC reference number and the date of receipt? This way if some newer files change status before older ones it will be easier to track and read. I know I asked this before but it doesn't seem to be working....

3. Can we have an additional data transfer column in the timeline status sheet? This would be for the Final determination and associated date from the CEC sheet. (column J and K)

Thank you!
 
Hi ,

Please indicate the columns references instead of using terms which are familiar to you.

1. Which column data entry should transfer the first piece of information to the Timeline Status tab ? Specify the column reference not the header label.

2. What is the information that should be transferred on this entry ?

3. Which column data entry should transfer the next piece of information to the entry which was made in 1 above ? Specify the column reference not the header label.

4. What is the information that should be transferred on this entry ?

Narayan
 
1. Column C should be the first transfer to the Timeline Status tab.

2. The transfer should be just the value in the cell. eg 2001/2016

*The transfer of the change in column E in the CEC tab to column C-M in the Timeline Status tab is fine so that can remain as is.

3. The next data transfer should be columns J and K in CEC tab to columns N and O in the Timeline status tab

The VBA error in the comment above was occurring when column B and F in the CEC tab had the same date.

thanks
 
Oh I forgot to tell you to to get column B to transfer from CEC tab into column B in Timeline tab as one of the first transfers.
 
That update looks good but I realized this one wasn't done too, from the above message.

The next data transfer should be columns J and K in CEC tab to columns N and O in the Timeline status tab. Thanks
 
Hi ,

Have you tried entering data in column J in the CECs tab ?

When you do so , that data gets transferred to column N in the Timelines Status tab ; the same happens in the case of data entry in column K in the CECs tab.

Narayan
 
Yeahhhhh!!!! This is awesome. Thank you a million! Is there any way to protect the codes on the sheet so no one can manipulate them? Besides protecting the entire sheet because people will have to access it to input their data
 
Hi ,

The tab named Timelines Status can be hidden , and the user need never know about it. It can also be protected with a particular setting so that the code can write to it , but users cannot.

The VBA Project can be protected with a password and that will be enough to keep most people away from it.

See this link for more details.

http://www.ozgrid.com/forum/showthread.php?t=20381

All this can come after the existing code has been thoroughly tested and found to be OK.

Narayan
 
I was just thinking that even though I do not want people to access the Timeline Status tab, they will need to, such as in cases of Public Holidays which are not accounted for in the CEC spreadsheet, we only considered working days. So in this case they can have access to the Timeline status tab to change the dates but just not the code.

Also I have a small issue with Column I in the CEC tab. Is there anyway to keep the formula applied to that column even when the value is removed? Say for example, someone deletes the value in column I "deadline date" , the formula is also deleted...how can i prevent this?
 
1. I need to remove column A in my CEC spreadsheet but this is affecting the data transfer into the timeline status. How can I fix this?
 
hi i need to make changes to the data transfer. I need to add more columns and add more data. can you tell me which parts of the code i need to change please? It's due tomorrow and i need to get this fixed today, please help. I tried deleting a column and that messed up the transfer....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim matchfound As Variant

If Target.Count > 1 Then GoTo exitHandler
If Target.Value = vbNullString Then GoTo exitHandler

Start:
If Application.Intersect(Target, Range("C2:C1000")) Is Nothing Then GoTo FirstStep:

With Worksheets("Timeline Status")
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
matchfound = Application.Match(Target.Offset(, 1).Value, .Range("A2:A" & lastrow), 0)
If IsError(matchfound) Then
.Cells(lastrow + 1, "A").Value = Target.Value
.Cells(lastrow + 1, "B").Value = Target.Offset(, -1).Value
End If
End With
GoTo exitHandler:

FirstStep:
If Application.Intersect(Target, Range("F2:F1000")) Is Nothing Then GoTo SecondStep:

With Worksheets("Timeline Status")
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
matchfound = Application.Match(Target.Offset(, -3).Value, .Range("A2:A" & lastrow), 0)
matchfound1 = Application.Match(Target.Offset(, -1).Value, Worksheets("Settings").Range("A3:A13"), 0) + 2

If IsNumeric(matchfound) Then
.Cells(matchfound + 1, "A").Value = Target.Offset(, -3).Value
.Cells(matchfound + 1, "B").Value = Target.Offset(, -4).Value

.Cells(matchfound + 1, matchfound1).Value = Target.Value
Else
.Cells(lastrow + 1, "A").Value = Target.Offset(, -3).Value
.Cells(lastrow + 1, "B").Value = Target.Offset(, -4).Value
.Cells(lastrow + 1, matchfound1).Value = Target.Value
End If
End With
GoTo exitHandler

SecondStep:
If Application.Intersect(Target, Range("J2:J1000")) Is Nothing Then GoTo ThirdStep:

With Worksheets("Timeline Status")
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
matchfound = Application.Match(Target.Offset(, -7).Value, .Range("A2:A" & lastrow), 0)

If IsNumeric(matchfound) Then
.Cells(matchfound + 1, 14).Value = Target.Value
End If
End With

ThirdStep:
If Application.Intersect(Target, Range("K2:K1000")) Is Nothing Then GoTo LastStep:

With Worksheets("Timeline Status")
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
matchfound = Application.Match(Target.Offset(, -8).Value, .Range("A2:A" & lastrow), 0)

If IsNumeric(matchfound) Then
.Cells(matchfound + 1, 15).Value = Target.Value
End If
End With


LastStep:
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal

If Target.Column = 4 Then
If oldVal = "" Then
Else
If newVal = "" Then
Else
Target.Value = oldVal & ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub
 
Hi ,

Sorry but your application has grown from one thing to another ; this is not the way to pose questions ; if you need a complete application , please start a new thread , and describe exactly what you want done , and specify everything at one go.

It is late at night for me , and I will not be able to help now. Hopefully someone else will.

Narayan
 
You must log in or register to reply here.
Back
Top