Referencing another sheet for selective information

edited 12/09/19

Any help with this would be appreciated.

I haven't been able to come up with a formula that would check a reference sheet for a value and pull the data from another column to populate the current form. I expect it would be a compound formula with an IF and maybe a INDEX,  but I'm having difficulty narrowing it down. - Any thoughts would be much appreciated.

Tags:

• ✭✭✭✭✭✭

It depends. Do you want the information consolidated? that is more difficult but possible. I've had some good luck with Join(collect(),",") then parsing the information using the "," between the values. If you want to mirror onto the other sheet it is very easy.

=IF(INDEX(SM:SM, 1) = true, INDEX(Title:Title, 1), "")

Replace SM:SM and Title:Title with column references to columns in the other sheet.

(Quick tutorial to reference another sheet https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets)

• Wow, that was fast (and "very easy"), however, I'm unsuccessful in iterating through the rows. I would have thought the series-fill (drag & fill) would increment the rows, but it doesn't. Should I be using a different way of referencing the related table/columns?

=IF(INDEX({Reference Range 2}, 1) = true, INDEX({Reference Range 3}, 1), "")

-Thanks again!

• ✭✭✭✭✭✭
edited 09/07/18

It is a bit harder to make it drop down.

You will need to keep your current formula in the first cell, then use a count reference.

=IF(INDEX(SM:SM, 1 + COUNTIFS(B\$1:B1, OR(ISTEXT(@cell), ISBLANK(@cell)))), INDEX(Title:Title, 1 + COUNTIFS(B\$1:B1, OR(ISTEXT(@cell), ISBLANK(@cell)))))

Post that in the second cell in the column and you can drag this formula down. You'll need to edit your references again though, but you can just copy paste those.

• ✭✭✭✭✭✭

I'm out for the weekend. Good luck and have a good one.

• This is really helpful as it shows me how to continue the series.

However, it leaves blank rows in the resulting list. This doesn't look so bad on 5 rows, but there will 50+ rows (in the reference file) with only several of the items checked.

Thanks again for your assistance, it's really close. Have a good weekend.

• ✭✭✭✭✭✭
edited 09/08/18

Hi David,

You could use a filter to only show the rows with relevant information.

Would that work for you?

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• edited 09/08/18

Thanks for your interest, Andree. I could filter, but I wouldn't be able to force the filter to be applied on a shared sheet.  I need to distribute to a team of users. - David

• ✭✭✭✭✭✭
edited 09/08/18

Happy to help!

You could set a default view with a filter applied or use a report if that would work.

https://help.smartsheet.com/articles/520104-share-sheets-reports-dashboards#defaultview

Best,

Andrée

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• This community forum is invaluable - this was the first time I posted something and within 48 hours had a fully functional solution. The combined solution (Luke's) formula and (Andree's) suggestion of using a sheet filter provided exactly what I needed.

I was able to use the Formula to sync 2 columns of content to the new document (whenever a checkbox is found in the Row of the Referenced sheet) and use Filters to remove any lines where both columns were blank. I applied the filter as "Default view" so that when I share the sheet, the filter is already applied.

Final Formula, 1st row (this checks IF Ref Range 1 is checked, and Syncs Ref Range 2 to the new sheet) :

=IF(INDEX({Reference Range 1}, 1) = true, INDEX({Reference Range 2}, 1), "")

2nd row and subsequent fill(ed) rows (this syncs the 2nd row and sets-up row counting to iterate through subsequent rows):

=IF(INDEX({Reference Range 1}, 1 + COUNTIFS([New Column]\$1:[New Column]1, OR(ISTEXT(@cell), ISBLANK(@cell)))), INDEX({Reference Range 2}, 1 + COUNTIFS([New Column]\$1:[New Column]1, OR(ISTEXT(@cell), ISBLANK(@cell)))))

New Sheet Filter, "Shows rows that match at least one condition" (Share filter) - [New Column] is not blank

Luke, Andree - Thanks again for the help with this!.

- David Tompkins

• ✭✭✭✭✭✭

It was my pleasure, David!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭

Hi David,

Could you explain how the formula below works? or could you share a sheet with the formula? Appreciate!

=IF(INDEX({Reference Range 1}, 1 + COUNTIFS([New Column]\$1:[New Column]1, OR(ISTEXT(@cell), ISBLANK(@cell)))), INDEX({Reference Range 2}, 1 + COUNTIFS([New Column]\$1:[New Column]1, OR(ISTEXT(@cell), ISBLANK(@cell)))))

@{Reference Range 1} and {Reference Range 2} should come from another sheet, what's the purpose of OR(ISTEXT(@cell), ISBLANK(@cell))? the "@cell" refers to a special cell in working sheet?

• ✭✭✭✭✭✭

Hi,

The @cell function looks at each cell in the referenced range.

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!