Formulas- Equivalent of an array?

April Urban

Hello! I am trying to write a formula that pulls information from one sheet into another based on a criteria. I have successfully written the formula to work for a single row, but can not get it to repeat for successive rows. I believe I'm looking for Smartsheet's equivalent to an array. I understand that a cell range is somewhat equivalent, and I believe the solution is in fixing how Smartsheet is creating a reference range, but I don't know how to fix it.

Here's my formula that's functional for one row:

=IF(NETWORKDAYS(TODAY(), {CZI Project Plan Range 2}) < 14, {CZI Project Plan Range 3}, 0)

CZI Project Plan Range 2 = a target end date column in another sheet, reference spans the whole column

CZI Project Plan Range 3 = what I want to feed into the cell. In the working formula, it's a single cell. I want it to be an entire column, but when I change the range to an entire column, the formula breaks.

Potentially helpful context 1- I'm comparing a project task target end date to today's date, and I want the sheet to populate with the task name if it's target date is within 14 working days.

Potentially helpful context 2- I'm doing it this way because I want this to populate a project agenda sheet. I realize I can easily get this information from filtering the project sheet and/or creating a report. But I can't feed that information into another sheet that I can functionally work with, so I'm trying this formula approach.

Thanks for any assistance!

Genevieve P.

Hi @April Urban

Cross-sheet formulas can only populate one cell, so you won't be able to bring back a filtered column list into another column, but we could bring back all the matching column values into one cell if that would work for you.

The way to do this is with the COLLECT function. COLLECT essentially filters down your results and gives you the column values based on your criteria. You can either use INDEX(COLLECT to find the first matching value, or JOIN(COLLECT to bring back ALL matches.

A JOIN(COLLECT works like this:

=JOIN(COLLECT({Column to Return}, {Criteria Column 1}, "1st Criteria", {Criteria Column 2}, "2nd Criteria"), ", ")

The ", " at the end is what will separate your multiple values.

In your case, try something like this:

=JOIN(COLLECT({CZI Project Plan Range 3}, {CZI Project Plan Range 2}, NETWORKDAYS(TODAY(), @cell) < 14), " / ")

Once again, this will populate all matches into one cell so you'll have one long string of task names. The other way to do this would be to have a unique ID that would help the formula find matches across sheets - then we could add this as the second column and criteria.

Cheers!

Genevieve

April Urban

Thanks! I think the index function might work better for this purpose. It seems like then I could populate some number of cells with this formula and maybe make a note that the list may be truncated if greater than the number of rows populated with the cell. I'll look into how to use the index function, and feel free to send more instructions and tips!

Espect

There is a work around regarding multiple criteria's in an array search - Tabulate the array and assign a value to each outcome - sumifs the value with the text or parameter as criteria's then index match that value to display the desired array outcome. This will remove the requirement to have nested if statements.

Example Code

=IFERROR(INDEX({Risk Rating Outcome}, MATCH((SUMIFS({Outcome}, {Array Search}, [Inherent Likelihood]@row, {Array Search 2}, [Inherent Consequence]@row)), {Outcome}, 0)), "Complete Inherent Consequence/Likelihood to define Inherent Risk Rating")