Auto-fill column when inserting a new row?

browne09

New Member
Joined
Sep 13, 2019
Messages
15
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??
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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)?
 
Upvote 0
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.
 
Upvote 0
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:
Code:
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).
 
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0
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? :LOL:
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top