Create a table referencing another sheet

HR

Hello Smartsheet experts I am looking for some guidance. I have one sheet "H&S Inspections" and now I'm trying to create an "H&S - Inspection Matrix" sheet.

My monthly inspections log is set up: "H&S - Inspections with Form"

What I want to do is create a table which shows that each location has completed a monthly inspection.

I've tried VLOOKUP and IF formulas but I just can't figure out how to get the formula right.

What I want to do is have the formula reference sheet "H&S - Inspections with Form". If an inspection was completed in "MAY" I want my tracking sheet to display the date the inspection was done.

All help greatly appreciated! 😀

Andrée Starå

Hi @HR

I'd recommend using an INDEX/MATCH combination instead, but if you want to use a VLOOKUP, you'd need to rearrange the Form sheet, so the Store Location column is the first in the range.

Make sense?

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots or the formula you're using? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

HR

Thank you for the recommendation Andree. I have no idea how to construct the Index/Match formulas. Do you have an example you could share?

Andrée Starå

@HR

Happy to help!

I'd be happy to share an example. 

Please send me an email at andree@workbold.com, and I'll share it with you.

Genevieve P.

Hi @HR

There are a few other Community Posts that go into how to construct an INDEX(MATCH as well, if you're looking for examples: https://community.smartsheet.com/discussion/comment/240214#Comment_240214

You may also want to look into a JOIN(COLLECT if you have multiple criteria: https://community.smartsheet.com/discussion/comment/239080#Comment_239080

In addition, you may need to add in something with the MONTH function: https://community.smartsheet.com/discussion/comment/250520

Hope this helps!

Genevieve

Genevieve P.

Hi @HR

I see that you marked my response as not answering your question, so I'll go into a bit more detail to see if that will help.

You could use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell, assuming that only one value meets all the criteria).

Try This:

=JOIN(COLLECT({Inspection Date Column}, {Store Location Column}, Month@row, {Inspection Date Column}, MONTH(@cell) = 5))

The way JOIN(COLLECT works is that you first list the range that has the value you want returned (your Date column in the source sheet), and then list each range and criteria afterwards.

The criteria for your Store Location column is that it matches the Location written in your Primary column of this sheet, which is labeled "Month". Then the criteria for your Date column is that the Month = May (or 5)

You will need to adjust the number in the formula that indicates which month per column, but everything else can stay the same. For example, this would be January:

=JOIN(COLLECT({Inspection Date Column}, {Store Location Column}, Month@row, {Inspection Date Column}, MONTH(@cell) = 1))

You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function

I'm happy to explain further if this didn't answer your question, please let me know.

Cheers,

Genevieve

Genevieve P.

Oh! As a quick, additional note, please make sure all of your columns are set up to be Date Type columns or it won't be able to return the date for you... see here! 🙂

Andrée Starå

@HR

Happy to help!

I saw that Genevieve answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

Paul Newcome

I actually suggest an INDEX/COLLECT instead of the JOIN. JOIN converts the incoming data into a text value, whereas INDEX maintains the data type. I would also suggest including a year criteria if you plan to have multiple years stored on the same sheet.

=INDEX(COLLECT({Inspection Date Column}, {Store Location Column}, Month@row, {Inspection Date Column}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2020)), 1)

You would just need to update the month and year numbers accordingly.

Genevieve P.

Hi @HR

I see that you have marked all these comments as not answering your question. Would you be able to provide us with more detail?

Did you try Paul's solution? If it didn't work for you, can you either show screen captures of the error message you're receiving or describe what incorrect result you are seeing?