Formula or VBA - Copy first data entered to another cell

[TU5JP4]

Hi guys!

Thanks for your time reading this, I greatly appreciate it.

I have a problem which may be possible to solve via formula or macro, either would be brilliant.

To give overview, the file is a tracker which monitors items over time. A sheet has two columns; 'Agreed Date' and 'Baseline Date'. Cells in the Agreed Date column are populated initially as the item is filled out across the row, and then updated as the item is tracked over time and the need to extend the completion date becomes apparent.

Cells in the Baseline Date column contain only the initial date entered in the Agreed Date cell of that row. This is so that the initially agreed date can be reviewed against the Agreed Date to monitor how the item is being managed by it's owner.

The Baseline Date column is not being completed thoroughly, as such an automatic solution which enters only the first date input to its adjacent Agreed Date cell is my desired solution. The data in the Baseline Date column should not then update as the Agreed Date is altered.

I'm a bit stuck with this one, and a poke around Google has brought nothing back - any help provided will be hugely appreciated.

Thanks,

Ben

 

[Roderick_E]

I'm a bit confused. So the user inputs an Agreed date in one column but some times the Baseline date column of the same row doesn't contain a date. Do you want to then put the Agreed date into the Baseline date column too if Baseline doesn't contain a date?

If so, I can make a "Worksheet_Change" solution for you. Thanks

 

[TU5JP4]

Hi Roderick, thanks for your reply!

Apologies I could have explained it far more clearly.

The process is that when the Agreed Date is first decided, that date must also be entered into the Baseline Date column cell for that row in order to keep a record of the initial date agreed. The date in the Agreed Date cell will be updated as time goes on, but the Baseline Date will always remain as the first date entered in both.

So effectively I need a function that copies the first value from one cell to another, but then does not update.

 

[Roderick_E]

No worries, here's the solution.
On the sheet you want this to happen, right-click the tab and VIEW CODE.
Copy and paste the following:

Function ColLetterFromNo(lngColNum) As String
    ColLetterFromNo = Split(Cells(1, lngColNum).Address, "$")(1)
End Function
Private Sub Worksheet_Change(ByVal Target As Range)
Dim basecol As String
Dim agrecol As String
'---------
[COLOR=#ff0000]agrecol = "A" 'set agreed date col[/COLOR]
[COLOR=#ff0000]basecol = "B" 'set base date col[/COLOR]
'---------
If ColLetterFromNo(Target.Column) = agrecol Then 'if the target col is the agrecol
If Trim(Cells(Target.Row, agrecol)) <> "" Then 'if the agrecol is not blank
If Trim(Cells(Target.Row, basecol)) = "" Then 'if the basecol is blank
Cells(Target.Row, basecol) = Format(Cells(Target.Row, agrecol), "Short Date") 'then make the basecol same as agrecol
End If
End If
End If
End Sub

Adjust the red lines to your needs.

 

[TU5JP4]

Brilliant, thanks! That’s not an approach I’m at all used to, so I can learn a lot from that!

There’s one small teething issue which I haven’t been able to sort out through column formatting, the date entered in Agrecol is transferred into Basecol in American format; 02/05/2018 entered manually becomes 05/02/2018 in the Baseline Date column and so on.

I’ve tried to find an alternative for “Short Date” but have had no luck and realise I’m probably looking at the wrong area anyway?

Thanks so much Roderick!

 

[Roderick_E]

Glad it helped. So, for the date issue; I'm sorry I didn't think about you wanting DD/MM/YYYY rather than MM/DD/YYYY.

Change my line to:

Cells(Target.Row, basecol) = Format(Cells(Target.Row, agrecol), "[COLOR=#ff0000]dd/mm/yyyy[/COLOR]") 'then make the basecol same as agrecol

 

[TU5JP4]

Perfect!

Cheers again Roderick, that'll save a lot of time.