Formula Help!

Mandy Maleta

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!

Paul Newcome

Can you explain this:

1_2_3_14, {Updated Copy_DAS Work Log Range 3}

Also... Which range is which column?

Your formula should end up looking very similar to this:

=JOIN(COLLECT({Source Sheet Zone Column}, {Source Sheet Venue Location Column}, "Newark EWR-TA", {Source Sheet System Type Column}, "iDAS", {Source Sheet Date Column}, AND(@cell <> "", @cell >= TODAY(-14))), ", ")

SteveReed@WK

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

Mandy Maleta

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

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

Mandy Maleta

I'm getting an #unparseable error. =/

Paul Newcome

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

Mandy Maleta

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}, ", "))))))

Paul Newcome

Can you explain this:

1_2_3_14, {Updated Copy_DAS Work Log Range 3}

Also... Which range is which column?

Your formula should end up looking very similar to this:

=JOIN(COLLECT({Source Sheet Zone Column}, {Source Sheet Venue Location Column}, "Newark EWR-TA", {Source Sheet System Type Column}, "iDAS", {Source Sheet Date Column}, AND(@cell <> "", @cell >= TODAY(-14))), ", ")

Mandy Maleta

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

Paul Newcome

Happy to help. 👍️