No Array formulas but need work around

Ramona · 02/22/19

Smartsheet does not use array formulas, but I need a way to list out items from one spreadsheet to another if a certain condition is met.

For example, I have an array formula that I used in excel that would look for a certain condition on one spreadsheet then pull all the rows of data that had that condition to another spreadsheet. In this case a date would be the condition, and I could change the date in a predetermined field and the array formula would look for all items that had that date then list them.

How can I do this in smartsheet? The below array is what I used in excel in the Order# column based on the shipped week 17 placed in the Green box. If I change the ship week the array pulls from another spreadsheet all orders based on that week. Is it possible to do this in Smartsheet? I've tried everything I can think of.

{=IFERROR(INDEX('[AUS GP Build Schedule.xlsm]Q2''19'!$C:$C,SMALL(IF(('[AUS GP Build Schedule.xlsm]Q2''19'!$AJ:$AJ=$M$3)*('[AUS GP Build Schedule.xlsm]Q2''19'!$AJ:$AJ=$M$3),ROW('[AUS GP Build Schedule.xlsm]Q2''19'!$C:$C)-ROW(INDEX('[AUS GP Build Schedule.xlsm]Q2''19'!$C:$C,1,1))+1),J3)),"")}

Paul Newcome · 02/25/19

Have you tried using a Report?

eric.o · 02/26/19

Hello,

You may be able to achieve the desired goal of pulling row data from one sheet to another, based on specific criteria, utilizing a combination of an INDEX(MATCH or a VLOOKUP.

These are further outlined here:

▸ INDEX: https://help.smartsheet.com/function/index

▸ MATCH: https://help.smartsheet.com/function/match

▸ VLOOKUP: https://help.smartsheet.com/function/vlookup

▸ CROSS-SHEET: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

The simplest way to reference the search value may be to reference a column on the recipient sheet containing the desired criteria, such as the "order #". Referencing this cell with the VLOOKUP or MATCH will ensure the correct criteria is always being referenced. It will also allow you to utilize the same formula for each new criteria entry.

NOTE: Sheet data can also be automatically referenced based on specific criteria utilizing a Report. https://help.smartsheet.com/articles/522214-creating-reports

Have a wonderful day,

Eric - Smartsheet Technical Support

Paul Newcome · 02/26/19

I can't say I completely agree with this. The original post is talking about pulling all rows that meet certain criteria. To use VLOOKUP or other means of cross sheet referencing requires that you have some kind of data already on the second sheet to compare to with the exception of cell linking which could very quickly become cumbersome and tedious depending on how many rows and columns you have.

I feel that (based on the original post) your second recommendation of using a report would be the best fit for this particular case.

eric.o · 02/26/19

Hi Paul,

Utilizing VLOOKUP you could very quickly and easily reference an entire row utilizing, for example, an Order # or product ID. You are correct, you would need this # or ID to be the reference for the VLOOKUP's to function. However, once the formulas are established the Order # would Auto-Fill the entire sheet. This would be the best option if they need to continue to perform calculations with the data, since Reports cannot utilize formulas.

You are correct that a Report would work as well. It all depends on the desired workflow. Both options would work. I appreciate your feedback.

Have a wonderful day,

Eric - Smartsheet Technical Support

Paul Newcome · 02/26/19

True. An order number or product ID can be used in conjunction with auto-fill to quickly populate a sheet for additional calculations. But that is dependent on some type of unique identifier per row.

The poster is asking to pull all rows that match a specific week number. A common identifier.

"In this case a date would be the condition, and I could change the date in a predetermined field and the array formula would look for all items that had that date then list them."

"If I change the ship week the array pulls from another spreadsheet all orders based on that week."

This very specific request COULD be accomplished with some rather convoluted and tedious formulas used for set up and an assumption that you will not exceed a certain number of entries using a JOIN/COLLECT to pull the data and then parsing it out.

VLOOKUP's are not however an efficient option in this case as the poster is looking to pull all rows that have a COMMON identifier as opposed to a unique identifier. If Smartsheet was able to efficiently parse data based on a delimiter, a VLOOKUP or INDEX/MATCH would be a great option as it would make further calculations much easier.

To get around not being able to run calculations off of a report, I use a separate sheet (usually called "Metrics", but that's just me). I then use formulas that are able to look at the same criteria as what I used for my report. The COLLECT function is VERY useful for this.

.

I agree... In some cases VLOOKUP or INDEX/MATCH can be a viable solution. Unfortunately though, because of Smartsheet's current parsing limitations, it would very quickly become bulky, cumbersome, and tedious if more than two or three rows match on that common criteria as specified in the original post.

No Array formulas but need work around

Comments

Help Article Resources

Categories