How do I pull dates from one sheet to populate onto another sheet based on an address

APALERMO

Good Afternoon, I am novice to Smartsheet and require some assistance. I have a sheet- let's call it Sheet A-that has a form linked to it. My colleagues complete this form when they go visits sites, and the information auto-populates onto Sheet A.

Screenshot 2024-04-24 123037.png

I have a second sheet-let's call this one Sheet B.

Screenshot 2024-04-24 123148.png

I copied and pasted all the sites' addresses from Sheet A to B. but now I want to pull the follow up dates from Sheet A onto Sheet B. I want to use the addresses as the criteria.

The formula I am trying to use is:

=INDEX({follow up date}, MATCH([Space Identifier]@row, {Office Address}))

Someone please help!!

Paul Newcome

Try this modification first:

=INDEX({follow up date}, MATCH([Space Identifier]@row, {Office Address}, 0))

APALERMO

I tried that as well, it does not work. I either get #NO MATCH (even when there is an address matching) or #INVALID COLUMN VALUE.

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

APALERMO

I was able to figure it out! The column type was incorrect.

This is the formula I used in the cells:

=INDEX({Follow-Up EOC Visit - Reports & Tracking Range 1}, MATCH(Office@row, {Office Address}, 0))

Is it possible to use this same formula, but now filter out dates and only have dates between 10/1/2023 to 9/30/2024?

Paul Newcome

You would need to change it to an INDEX/COLLECT.

=INDEX(COLLECT({Follow-Up EOC Visit - Reports & Tracking Range 1}, {Office Address}, @cell = Office@row, {Date Range}, AND(@cell>= DATE(2023, 10, 01), @cell< DATE(2024, 10, 01)))

APALERMO

Hmm i am still having issues.

In your formula, is "{Date Range}" just typed in? Or is this referencing a column on another sheet?

Paul Newcome

You would have to create a cross sheet reference to the date column in your source sheet the same way you created the other cross sheet references.