Reference Entire Sheet AND Filter Rows Where Column Value ="Search Value"

kmforecasting

I am simply trying to do the following:

• - Refer to another sheet (ALL ROWS and COLUMNS) with the following conditions:
• - Where in Column [STATUS] is = "Done"

The RESULT should be:

• - This new sheet shows all rows and columns from another sheet where Status is Done.

Jeff Reisman

Why not just create a report based on the original sheet?

Set the Report to Filter by Status = "Done"?

Create a new Report. Select the sheet(s) you want to include. Select columns to include. Set Filters, Group By, Summarize, and Sort options, if desired.

Keep in mind that Reports in Smartsheet are live data, so if you delete data in a report, it deletes in the underlying sheet (unless the row or column is locked and you don't have Admin or Owner access.)

kmforecasting

@Jeff Reisman Good idea. However, I need to use the metrics tool for the dashboard we are creating later on.

Jeff Reisman

Additional details about your use case will get you better answers from this community.

kmforecasting

@Jeff Reisman Simply just show all columns and rows in new smartsheet thats derived from first smartsheet WHERE Status = "Done"

Andrée Starå

Hi @kmforecasting

I hope you're well and safe!

To add to Jeff's excellent advice/answer.

• You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Would that work/help?

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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

kmforecasting

How do I index/match an entire sheet to include all rows and columns

kmforecasting

Jeff Reisman

To use INDEX/MATCH (real time references):

Create a copy of Sheet 1 and erase the data in the copy (Sheet 2)

In each column:

INDEX({Reference Column you're looking up from Sheet 1}, MATCH("DONE", {Reference Sheet 1 Status column}, 0))

Alternatively - to copy rows from Sheet 1 to Sheet 2 when the status equals DONE:

Create a copy of Sheet 1. Filter by status, find any row not DONE, and delete it.

Create a hidden Checkbox column on Sheet 1, called "Copied"

Create an Automation rule in Sheet 1.

Trigger: When a row changes, when Status changes to DONE. Run workflow when triggered.

Condition: Where Copied is not checked

Action1: Copy rows to Sheet 2

Action2: Change cell value - change Copied checkbox to checked

Andrée Starå

@kmforecasting

To add to Jeff's description.

I'd recommend adding an Autonumber Column in the Source sheet and adding a Text/Number column where you add the number you anticipate would be needed in the Destination sheet. Then you use it for reference to connect the sheet.

Make sense?

Would that work?

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

Jeff Reisman

To use INDEX/MATCH (real time references):

Create a copy of Sheet 1 and erase the data in the copy (Sheet 2)

In each column:

INDEX({Reference Column you're looking up from Sheet 1}, MATCH("DONE", {Reference Sheet 1 Status column}, 0))

Actually, this won't work. You'll end up just repeating the first matching row from Sheet 1 over and over.... You need a unique value shared between the two sheets to do this. What Andree said ☝️