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!
Best Answer
-
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))), ", ")
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!
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.
-
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!
-
I'm getting an #unparseable error. =/
-
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!
-
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}, ", "))))))
-
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))), ", ")
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!
-
ok thank you! I think I'm on the right track now.
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!