Best Of

Celebrating Smartsheet Community

I got these excellent gifts last week in recognition of Leader status here and while I love the swag and badges, I want to emphasize how genuinely enriching it is to connect with all of you here! It sincerely makes my day when people here share for the greater good. Thank you @Arsineh @Alison Clancy @Rebeca S. and all of the Smartsheet Community team for making this an easy, fun place for us! I’m completely addicted to being here and can’t wait for Ambassador and beyond! (This may or may not be my 100th post 😎…only six-billion, forty-eight thousand, eleventy-two more to go until I change my name to Paul Jr.)

Much 🫶🏻 to you all!

Will

Will Jeffords

7 ·

Re: Remove #NO MATCH return from VLOOKUP query

You can wrap your formula in an IFERROR function.

=IFERROR(Your formula, "")

https://help.smartsheet.com/function/iferror

Mike Wilday

1 ·

Re: Smartsheet Forward Q2 2024: New ways to unlock actionable insights

thanks @Ben Canning, these are exciting updates and feature areas all around! Fun to explore and put to productive use!

Will

Will Jeffords

1 ·

Re: How do I pull in a value using 2 criteria?

@Gillian C

Thank you very much! The formula worked when I changed the 0 to a 1.

Follow up questions, some of the projects are not in my reference sheet, so I am getting an #invalidvalue. Is there a way to either return a 0 or remove these? I applied the formula to my entire column.

Jess A

1 ·

Re: How do I pull in a value using 2 criteria?

@Gillian C Thank you so much! You are a life saver!

Jess A

2 ·

Re: Nested Index Collect to gather data from multiple sheets

Hey @Jess D

Try this

=IFERROR(IFERROR(INDEX(COLLECT({Sheet1 Person}, {Sheet1 Client}, Client@row),1), INDEX(COLLECT({Sheet2 Person}, {Sheet2 Client}, Client@row),1)),"Missing Person")

Will this work for you?
Kelly

Kelly Moore

1 ·

Re: VLOOKUP and COUNTIFS

Got it, I would try something like:

=COUNTIFS([Issue Location]:[Issue Location], "Cath/IR & CP Restock Staff (b)", [Month Reported]:[Month Reported], Month@row)

If this grid is on a separate sheet the ranges will need to reference the sheet column in place of the ranges I put in [brackets].

Dakota Haeffner

1 ·

Re: VLOOKUP and COUNTIFS

Hey @shelbylund,

I would write it like so:

=COUNTIFS([Month Reported:[Month Reported], "May", [Issue Location]:[Issue Location], CONTAINS("Cath/IR", @cell)

You'll want to change the cell ranges to sheet references from the source sheet (and change the month/issue location to match the cell the formula is in), but this structure should work!