Formula Help!

I'm creating a dashboard that contains some metrics. I have my metric worksheet already setup and have pulled over info. I'm needing a particular formula and cannot seem to figure it out. Here is what I'm working with:

I need to search one column of information for any input values for only the past two weeks IF a different column contains a certain name.

Column A: Company Names

Column B: Dates

Column C: # values from 1-10

To visualize I need to pull the # values from column C for the last two week (column B) only if column A contains a particular company name.

Is this possible? I've been searching and searching for over a week now and have tried so many different formulas. Thanks for any and all help here!

Tags:

Best Answer

Answers

  • Mandy,

    In my opinion, the best way to address your situation is not through a formula, but by using a report. Here is what I did:

    • Created a sheet with your columns and some bogus data
    • Created a report to pull the data per your conditions (as seen below)

    It appears that it is returning the correct results to me. Let me know if this is what you were looking for or if you have questions.


    Steve


  • Thanks, Steve! It sounds like a great plan, but when I went to do it apparently I'm not an admin..? Which doesn't make much sense to me since I'm the one that created the reports..

    Is there are formula that would say IF(company name = A) AND (date = today-14) THEN (Zone = 4, 7) or whatever zone numbers are applicable per the input data.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try a JOIN/COLLECT like so...

    =JOIN(COLLECT([Zone Column]:[Zone Column], [Company Name]:[Company Name], "Company A", [Date Column]:[Date Column], AND(@cell <> "", @cell >= TODAY(-14)))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • I'm getting an #unparseable error. =/

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you provide the EXACT formula you have in your sheet along with the exact names of each column to be referenced?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Sorry for the delay here! I was waiting on some additional info.

    The columns that I am working with are:

    Start Date of Activity (will need to be within the last two weeks from current date), Venue Location( will need to select "Newark EWR-TA"), System Type (will need to select "iDAS"), Zone (trying to pull whatever zone numbers are in the cell(s) after running the other criteria)

    =JOIN(CONTAINS(1_2_3_14, {Updated Copy_DAS Work Log Range 3}, and(IF({Updated Copy_DAS Work Log Range 1}, "Newark EWR-TA", and(IF({Updated Copy_DAS Work Log Range 5}, <=TODAY(), {Updated Copy_DAS Work Log Range 5}, >=TODAY(-14), ({Updated Copy_DAS Work Log Range 3}:{Updated Copy_DAS Work Log Range 3}, ", "))))))

  • ok thank you! I think I'm on the right track now.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!