Array formulas

Costinel badescu

Hi,

I was looking for using array formula functionality in smart sheet.

I need this because i saw that like this i can full fill what i need.

Any case, could you please mention if is a way to use array or other solutions in order to perform:

- I want smart sheet to show me in a column date, the first date, closest that will happen in the future.

(in my example i used  MIN(date) but is not ok)

- I want that  where the Status will change to "Done", smart sheet not to take care about that date, and to show me the next closest date. That will be my next D-day to take care of it.

 

Would be wonderful and appreciate a lot your support in this.

Thank you!

Costinel

 

Paul Newcome

Are you trying to pull the nearest date for the tasks that are marked as "In Progress"?

Vivien Chong

@Paul Newcome I have a similar question. If from the example above, I need to pull the nearest dated for the tasks marked "In Progress", how should the formula be written?

And if I change the D-day to % Completed, wanting to pull the least percentage to the highest percentage with tasks marked "In Progress", how should it be?

Appreciate your assistance and input.

Paul Newcome

@Vivien Chong I'm not sure I follow. By "nearest date" do you mean the closest date AFTER today, or just any date that is closest to today?

Then I am not sure exactly what kind of end result you are looking for with the percentages.

Are you able to mock up some example data and then manually enter the end result so I can see what we are working towards?

Vivien Chong

Hi @Paul Newcome

This is a better scenario.

A base sheet containing the following: (M1, M2, E1, E2 being the job band)

Another reference sheet is as follows:

What formula can we use to pull the "Training need to attend" in the reference sheet?

Appreciate your assistance. Thanks.

Paul Newcome

To make sure I am clear...

You want to manually enter "E1" and "Production" then have it pull from the [Training] column on the 1st sheet based on the checkboxes?

Vivien Chong

@Paul Newcome yes, you are right.

There will be a base sheet containing all departments and job band with the associated job training that they will need to attend.

So the HR team wanted to show the list of training that they should plan for new staff.

Also, these trainings will be tabulated individually to know how many training has been conducted.

At the moment, this is the best way, moving on from Excel, to get it going soonest. Previously in Excel, they are using array formula.

I am also wondering if the data structure structure is correct and able to achieve the desired results.

Appreciate your expertise in working out how we can work around the array formula here.

Thanks Paul.

Paul Newcome

Ok. This is going to take a little bit of working/testing, and I have been rather busy lately. I will work on it when I can and then let you know as soon as I am able to provide some sort of solution. I have some ideas. I just need to figure out the details.

Vivien Chong

Thanks @Paul Newcome . It is good that you have some ideas. I am totally lost. haha.... Take your time. Hear from you again. Have a blessed day!

Paul Newcome

Ok. So if you are ok with the training needed listing being in the same cell (we can use a line break and wrap text), then we should be able to use a single helper column on the reference sheet.

Make this helper column a text/number type column and use the following formula:

=Dept@row + "," + JOIN(COLLECT($[M1]$1:$[E2]$1, $[M1]@row:$[E2]@row, 1), ",")

Then in the working sheet I used "ColumnA" as the working column name since I can't see your column names in the screenshots. The formula would look like this...

=JOIN(COLLECT({Reference Table Training}, {Reference Table Helper}, AND(CONTAINS(ColumnA3, @cell), CONTAINS(ColumnA4, @cell))), CHAR(10))

So the reference sheet would look like this:

And the working sheet:

Vivien Chong

@Paul Newcome thanks for the insight. will test this out.

For this formula, what does the CHAR(10) at the end means?

=JOIN(COLLECT({Reference Table Training}, {Reference Table Helper}, AND(CONTAINS(ColumnA3, @cell), CONTAINS(ColumnA4, @cell))), CHAR(10))

Andrée Starå

Hi @Vivien Chong

CHAR(10) is used to add a line break.

More info.

https://help.smartsheet.com/function/char

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

Paul Newcome

Yes. CHAR(10) is a line break. It isn't EXACTLY how you had it in your screenshot where each piece is pulled into its own cell, but when you enable text wrapping on the cell it appears that way.

If you do in fact need it parsed so that each piece is in its own cell, that is possible, but will require a little more complexity and a few more questions.

Vivien Chong

Thanks @Andrée Starå & @Paul Newcome . I learn something new today: CHAR.

Have a blessed day!

Andrée Starå

@Vivien Chong

Excellent!

You're more than welcome!

Paul Newcome

Happy to help. 👍️