Unable to get formulas to work

Options

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 :)

Best Answer

«1

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • PJay128
    PJay128 ✭✭
    Options

    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
    Options

    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

    Projektmanagement Professional in Frankfurt am Main, Germany

  • PJay128
    PJay128 ✭✭
    Options

    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
    Stefan 7k ✭✭
    edited 12/16/19
    Options

    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 😉

    Projektmanagement Professional in Frankfurt am Main, Germany

  • PJay128
    PJay128 ✭✭
    edited 12/16/19
    Options

    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
    Options

    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

    Projektmanagement Professional in Frankfurt am Main, Germany

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @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)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • PJay128
    PJay128 ✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    edited 12/16/19
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    @PJay128 Excellent!

    Glad you got it working!

    Let me know if I can help with anything else!

    I'm always happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • PJay128
    PJay128 ✭✭
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    Aha! I thought you were all done! 😉

    I'll take a look again!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • PJay128
    PJay128 ✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!