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))), ", ")
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)))
-
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?
-
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))), ", ")
-
ok thank you! I think I'm on the right track now.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!