Unable to get formulas to work

PJay128

I'm trying to pull all the information from one sheet where audits are recorded and moving it onto another to simply show if the audit has been done or not.

Sheet displaying completion (Just for Site, Silo, 2019)

Source sheet contains 3 columns i need to confirm are true on a row to populate the completion sheet:

Business - site

Audit - silo audit

Date - 2019

I thought i would start off simple (lol) by just finding the site and the date using:

=IF(FIND([Site]@row, JOIN(COLLECT({Heath & Safety Audits Range 1}, {Heath & Safety Audits Range 2}, AND(IFERROR(YEAR(@cell), 0) = 2019, ",")) > 0, "Yes", "No")))

This is in [Silo Audit]1 (above).

Am i using the right function?

Thankyou in advance :)

Andrée Starå

@PJay128 Try something like this.

Add another range, Heath & Safety Audits Range 3 for the Audit column.

For the Elevator status: 

=IF(FIND(Site@row, JOIN(COLLECT({Heath & Safety Audits Range 1}, 
{Heath & Safety Audits Range 3}, (@cell) = "Elevator"), ".")) > 0, "Yes", "No")

For the Skip System status:

=IF(FIND(Site@row, JOIN(COLLECT({Heath & Safety Audits Range 1}, 
{Heath & Safety Audits Range 3}, (@cell) = "Skip System"), ".")) > 0, "Yes", "No")

Did it work as expected?

Andrée Starå

Hi,

Depending on how you want to show the information on the other sheet, you could use cell-linking instead.

If that won't work, will the Site name be unique on the source sheet, or is there anything else on the sheet that will be?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

PJay128

I was planning on showing the information as a yes / no and only for 2019. Next year i will create a separate sheet for 2020.

The site name will be what i want to find in the source sheet. This would be duplicated as audits are annual.

Once i find that i need to find out if the year is 2019 (system generated column) and if the audit completed was the Silo Audit (dropdown column) all those being correct we get a green tick.

:)

Stefan 7k

Hi PJay128,

Andrée already asked good questions I am adding curiosity ;-)

I understand, that information about all your audits live in one sheet. If this is right, why not use conditional color or an icon to visually show if an audit has been completed? Or you could use a filter showing only completed audits, if overview in a large sheet is the issue.

Hope that helps and greetings

Stefan

PJay128

Cheers Stefan,

There are around 100+ sites which need to complete audits the issue I have is spotting the one missing :)

My plan was to create a sheet which would be published so everyone can see a live update of the sites and the audits they have completed.

The source audit sheet has data which shouldn't be viewed by everyone.

There are also some audits on different sheets which would eventually benefit from being on the completion sheet.

Stefan 7k

Hi,

ah ok, now I understand better.

Thought about using a report and a Dashboard showing this report? The report in the dashboard does not automatically link to the source sheet, so no issue with confidentiality. The dashboard can be published like any sheet.

And the report can dynamically show only relevant data from your source sheet matcheing specific conditions. If you sort the results by "Site" first and then by "Audit", you should get a got overview per site.

Hope this helps and greetings

Stefan

PS: By the way, I love using formulas and functions in Smartsheet. I simply do not see the necessity here 😉

PJay128

If i used a report it would only tell me who has done the audit.

It would therefore be a case of trying to remember which sites were missing from the report.

So on my Completion sheet i was going to put a column with all the 100+ sites going down then across the top the audits required.

Then the formula would check the site to see if the audit has been done. I would then end up with green ticks and red crosses. This displayed on a website its very visual to all who has and who hasn't completed their tasks.

Maybe i should have mentioned the source sheet contains the raw data from the audit questions.

So 1 row is an audit

Each site should have 2 entries a silo audit and an elevator audit for 2019

So the completion table would tell the site manager hes done both or none or only 1 of the audits.

Stefan 7k

Hi,

to be honest, I usually never find 100+ row tables very helpful with all the involved scrolling... ;-)

Assuming, that preparing for an audit not only relies on site managers looking into Smartsheet, I think what might be more important are the sites not having done their audits. Agree?

So maybe a report only about those not completed would help? And no, a report does not only show who has done the audit or not, it can show both and more. Play around a little :-)

Greetings

Stefan

Andrée Starå

@PJay128 Can you share screenshots from both sheets or maybe share the sheet(s) or copies of them? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

PJay128

Cheers Andree,

For info i managed to fix the formula to provide a Yes or No if the date was 2019 damn stray comma!

=IF(FIND(Site@row, JOIN(COLLECT({Heath & Safety Audits Range 1}, {Heath & Safety Audits Range 2}, AND(IFERROR(YEAR(@cell), 0) = 2019)), ",")) > 0, "Yes", "No")

Paul Newcome

I would agree with Stefan on this one. A report could be very helpful. Instead of setting up the report to show completed, set it up to show incomplete.

In regards to your formula though, there are a few syntax issues. You would want to try something like this instead...

=IF(FIND(Site@row, JOIN(COLLECT({Heath & Safety Audits Range 1}, {Heath & Safety Audits Range 2}, IFERROR(YEAR(@cell), 0) = 2019), ", ") > 0, "Yes", "No")

Andrée Starå

@PJay128 Excellent!

Glad you got it working!

Let me know if I can help with anything else!

I'm always happy to help!

PJay128

hehe thats just to find the year.

Id also like the formula to check 2 additional columns on the source sheet.

The business and the type of audit completed both text fields

:(

Andrée Starå

Aha! I thought you were all done! 😉

I'll take a look again!

Andrée Starå

@PJay128 Try something like this.

Add another range, Heath & Safety Audits Range 3 for the Audit column.

For the Elevator status: 

=IF(FIND(Site@row, JOIN(COLLECT({Heath & Safety Audits Range 1}, 
{Heath & Safety Audits Range 3}, (@cell) = "Elevator"), ".")) > 0, "Yes", "No")

For the Skip System status:

=IF(FIND(Site@row, JOIN(COLLECT({Heath & Safety Audits Range 1}, 
{Heath & Safety Audits Range 3}, (@cell) = "Skip System"), ".")) > 0, "Yes", "No")

Did it work as expected?

PJay128

Cheers Andree that works just need to try and add the year to the same formula :)