How to autofill a date from another sheet into a column

Carlene Smith

Is there a formula to autofill a date from another sheet into a column?

I currently have a sheet with dates for quarterly tests (Sheet A). I also have a sheet that information is entered via a form (Sheet B). What I would like to happen is when an employee fills out the form and it populates on Sheet B, the quarterly test column autofills with a specific date from Sheet A. Is this possible?

Example columns

Sheet A

Sheet B

Since I've been unable to figure out a formula, I set up an automation to notify me when there is a new entry, so that I can fill out the date column.

Matthew Flebbe

Hi Carlene,

I do this by creating cross sheet references. You can start by creating an "IF statement on Sheet B:

When you get this far click that "Reference Another Sheet" link and select Sheet A, click on the cell you would like to reference and give it a name:

You may get en error if your date field is restricted to dates only. Just click allow:

Now you can finish your formula by double clicking on the cell:

You might get that same error about dates again. To finish this off, just right click on your cell and convert this to a column formula:

Now every new line will be auto-filled with your cross sheet reference. Repeat these steps for your other dates.

Hope that helps!

Matthew Flebbe

Hi Carlene,

I do this by creating cross sheet references. You can start by creating an "IF statement on Sheet B:

When you get this far click that "Reference Another Sheet" link and select Sheet A, click on the cell you would like to reference and give it a name:

You may get en error if your date field is restricted to dates only. Just click allow:

Now you can finish your formula by double clicking on the cell:

You might get that same error about dates again. To finish this off, just right click on your cell and convert this to a column formula:

Now every new line will be auto-filled with your cross sheet reference. Repeat these steps for your other dates.

Hope that helps!

Carlene Smith

It worked!!! Thank you!!