Welcome!

Referencing a date from another sheet

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

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

I am getting an error message when attempting to export to excel
I am attempted to export to excel. I have two different smartsheet documents to export. One of them exported to excel fine, but not the other. This happened a few weeks ago. I make sure all columns were the correct size & no cells were hidden. "We have found a problem with (file) Do you want us to try and recover as much…
Need Help w/ Report View - Nothing Is Showing
Hello Smartsheet Community, I need some help with a report that I created and shared with a customer. The customer is not seeing any of the information that is pulling into the report. See screen shot below that the customer texted me. I do have the report filter only showing the customer their product pulling from our…
How can I hard code a cell based on a trigger?
BLUF: When I create an Invoice, I need the cell-link to my live price data to be severed. I currently have a Price sheet for my products that is updated frequently. I use Control Center to create Projects, which includes an Invoice sheet. I have a dropdown on my invoice sheet to select a product, and then I use Index…