Referencing a date from another sheet

racerx45

Hello,

I am trying to create a formula to pull a date from a row on another sheet and show that date.

Here is the description of the two sheets:

Sheet ‘A’ is tracking shipments for a specific project and where I need the date to show up. This is a new sheet I am trying to implement.

Sheet ‘B’ is a list of all projects under a given Sales Rep. It has 23 columns and the row count will vary.

So I need the formula to reference a specific sheet ‘B’, select the correct row and display the date from column #12.

The first column on sheet ‘B’ is called ‘Project Name’, ideally I would like to enter the project name in a cell on sheet ‘A’ and then have the formula reference a specific sheet ‘B’ find the correct row and display the date from column #12.

 

Thanks in advance for the help

Betsy Green

I haven't finished this training yet, but it may be helpful to you: https://learn.smartsheet.com/advanced-functions-cross-sheet-formulas-in-smartsheet

Paul Newcome

You are going to want to use a VLOOKUP function with some cross sheet referencing. Something along the lines of:

 

=VLOOKUP([Project Name Column]@row, {Sheet B Range 1}, 12, false)

 

[Project Name Column] would be replaced with the column name in which you are entering the project name on sheet A.

 

{Sheet B Range 1} would be the cross sheet reference to sheet B where you select all columns starting with the project name column on the left and ending with the date column on the right.

 

12 is the column number that contains the data you want pulled from Sheet B with the leftmost column of your range being 1.

 

false means that your range isn't sorted in any specific order. Even if it is, I have found that (generally) I get the most accurate results consistently using false.

 

You would put this formula in the column on Sheet A where you want the date to be displayed.

racerx45

Thanks Paul that helped. Here is the formula I end up with and it works as expected

=VLOOKUP([Purchase Order Number]3, {Bill M. CPR Range 1}, 12, false)

My mistake was trying to use the column name from the sheet where I want to look the info up instead of the column on the sheet with the formula.

Paul Newcome

Happy to have helped.

Traci Goble

I am trying to this very same thing along with an automation workflow. I put the formula in and it works, however when I enter the criteria to trigger my automation I loose my date and my automation to move a row goes into the sheet about 328 lines down??? Any advice? I am about to loose my mind!

Genevieve P.

Hi @Traci Goble

Could you provide a little more detail on how your process should be working? It would be useful to know exactly what the trigger is for your automation and more information on what date is being lost. Screen captures of your set up may help, but please block out any sensitive data.

In regards to the row appearing 328 rows down, keep in mind that newly created rows (such as ones created from a form or from a Move Row automation) need a completely fresh, new row to populate into. If the 300+ rows above previously had data in them that was deleted out, these will be seen as rows-with-content as they have a Cell History. Try fully deleting the rows from the sheet instead of just clearing out the data. Then the Move Row should populate higher up. Hope this helps!

Cheers,

Genevieve