How do I pull specific dates from another sheet?

Erica H

Hello, I'm new to smartsheet. I'm looking for a formula to pull in dates for specific countries. I have multiple column headers (milestones) for each country. I would like to look up the country in another sheet, then pull in the dates from each column header (milestones).

Here is an example of the sheet I'm pulling from. I will have more columns with more dates, but right now I only have 2 columns.

Here is an example of the sheet I will pull this data to:

Georgie

Hi @Erica H,

You can use a nested IF formula with INDEX(MATCH) to achieve this.

First of all, though, you’d need to add a new row in your source sheet and add your column headers there. This is because we need to reference these cells in the formula - formulas cannot reference column headers. I’d suggest doing this in the top row and formatting it so that it’s clear it’s different from the sheet data. Your source sheet would then look something like this:

Then, in your target sheet, in the column you want to pull the dates into, you can use the formula:

• =IF([Task Name]@row = {Index Match source Range 2}, INDEX({100% Parity Date}, MATCH(Country@row, {Country}, 0)), IF([Task Name]@row = {Index Match source Range 3}, INDEX({Forecasted Last Day of Sales}, MATCH(Country@row, {Country}, 0))))

The sections in curly braces {} are cross-sheet references - you can create these by clicking the “Reference another sheet” link when you’re typing the formula into the cell. So, for the {Country} reference, you’ll want to locate your source sheet and select the entire Country column. You can rename the sheet reference at the top of the window - see the Reference Another Sheet window below:

The {Index Match source Range 2} reference is the cell in the source sheet with the value “100% Parity Date”, and the {Index Match source Range 3} reference is the cell in the source sheet with the value “Forecasted Last Day of Sales”. 

Once you’ve created your formula, you can right-click on the cell and select Convert to Column Formula to apply it to all rows.

The formula will check what value is listed in the “Task Name” column in the same row in the destination sheet, match that with the corresponding value in the first row of the source sheet, and pull through the date from the relevant column - here’s my destination sheet:

For more information on the functions used in this formula and creating cross-sheet references, check out the following help articles:

• IF Function
• INDEX Function
• MATCH Function
• Formula combinations for cross sheet references
• Create cross-sheet references
  

Hope that helps!

Georgie

Erica H

Hi Georgie!

Thank you so much for your support here, much appreciated! You gave a great explanation! Before I try this, I do have a question. Rather than restricting the data to rows, is it possible to pull the dates by country? My fear is that the rows could change because I will need to upload a new source file monthly (this is bc the source file is created from other multiple source files, unfortunately). I've read about the INDEX, COLLECT function. Would this allow me to pull by country w/out having to call out specific rows?

Georgie

Hi @Erica H ,

INDEX(COLLECT) can be used to return an item from a range based on the values that meet the specified criteria (check out the INDEX Function help article for an example). 

For your use case, since you’ll have multiple rows with each country and it sounds like you want to return the dates from the latest row for each country, I believe the best way to do this is to add either a Created Date column or an Auto Number column in your source sheet and then a helper column in your destination sheet.

Once you’ve added your Created Date or Auto Number column and saved the source sheet to populate the column, you can create a helper column in your destination sheet and use a formula with the MAX function and the COLLECT function to return the row that has the latest data for each country.

As an example, I added an Auto Number column called Row ID in my source sheet, as seen below:

I then adjusted our original formula to use INDEX(COLLECT) instead of INDEX(MATCH), as you suggested. The final formula I used is:

• =IF([Task Name]@row = {Index Match source Range 2}, INDEX(COLLECT({100% Parity Date}, {Row ID}, [Latest row for each country]@row), 1), IF([Task Name]@row = {Index Match source Range 3}, INDEX(COLLECT({Forecasted Last Day of Sales}, {Row ID}, [Latest row for each country]@row), 1)))

You can see below that this is returning the latest dates for each country for the relevant task name:

Let me know if you have any more questions on this!

Georgie