Referencing another sheet for selective information

David Tompkins

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.

L_123

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)

 

David Tompkins

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!

L_123

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.

L_123

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

David Tompkins

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.  

Andrée Starå

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 

David Tompkins

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 

Andrée Starå

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

David Tompkins

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

Andrée Starå

It was my pleasure, David!

Hawk

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?

 

 

Andrée Starå

Hi,

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

More info: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell#cell

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting