Auto-fill column when inserting a new row?

[browne09]

I've got a spreadsheet with a hidden 'start date' column. The start date is going to be the same for all tasks that are inserted into the sheet, so I want it to automatically fill this date in when people insert a new row.

Is this possible??

 

[Joe4]

When they insert a new row, are they entering in data somewhere?
If so, then one way to do this would be when they enter something in a certain column, it automatically checks the value in another column, and if that column is blank, populate it with the desired data.
Does that sound like it would work?

If so, please provide us with the following:
- What column do you want "triggering" this to run when updated?
- What column is supposed to be updated?
- What date goes in this column (today's date, or some other date)?

 

[browne09]

When they insert a new row, are they entering in data somewhere?
If so, then one way to do this would be when they enter something in a certain column, it automatically checks the value in another column, and if that column is blank, populate it with the desired data.
Does that sound like it would work?

If so, please provide us with the following:
- What column do you want "triggering" this to run when updated?
- What column is supposed to be updated?
- What date goes in this column (today's date, or some other date)?

Yes that sounds like it will work! Thank you! I would upload a screenshot but for some reason I can't do it on my computer.

So, I have a "Start Date" column (column B) that will need to be automatically updated with the same date each time. This is 01/09/2019.

The column that other data is being entered into will be the "End Date" (column C), so that deadlines/duration of tasks can be calculated etc.

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and then paste this VBA code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   See if updates cell in column C
    Set rng = Intersect(Target, Columns("C:C"))
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells in column C
    For Each cell In rng
'       Exclude title row
        If cell.Row > 1 Then
'           Populate column B with date
            If cell <> "" Then
                Application.EnableEvents = False
                cell.Offset(0, -1) = DateSerial(2019, 9, 1)
                Application.EnableEvents = True
            End If
        End If
    Next cell
    
End Sub

This will automatically populate column B when column C is populated (skipping row 1, assuming title row).

 

[browne09]

Amazing! Thank you so much!!!

If I wanted to apply this to other worksheets but instead of copying the date it copied the formula, how would I go about it?

 

[Joe4]

If I wanted to apply this to other worksheets but instead of copying the date it copied the formula, how would I go about it?

I am not sure I understand what you are saying about a formula...

 

[browne09]

So I have another sheet with a table that has these headings:

Project -- Project Owner -- Days Until Deadline -- Project Support

In project owner, days until deadline and project support there are formulas so that the table reads from a Master sheet and pulls the information through, so that it will automatically change when changed on the Master. I am trying to have it so that when someone inserts a new row to enter a new project, these formulas automatically appear in the new row.

Does that make sense or have I confused it even more?

 

[Fluff]

Cross posted multiple sites.

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide links to all other sites where you have asked this question.

 

[Joe4]

Please provide links to ALL the other places where you have posted this question.
I do not wish to waste time creating code for this if the question has already been answered somewhere else.

 

[browne09]

Apologies!!! New to this and tried to post earlier but didn't work for some reason.

Here are the links:

https://www.excelforum.com/excel-pr...mula-when-inserting-new-rows.html#post5196878

It is also posted in the Reddit Excel forum but I cannot get access to it right now so will add it in once I have got it.

This is no longer an problem as I have decided not to pursue it and just stick with dragging down the cells.

I am very appreciative of your help