Pull a calendar
Hi-- so, I have a series of columns representing various tasks. And then I have a series of rows representing various projects. I enter various dates into the corresponding cells (eg Project X and Task Y will occur on such and such date).
What I want to do then is on a separate sheet list all of the dates in the year and then have Smartsheet automatically pull the tasks/projects associated with a given date. So some dates would have nothing associated, and others might have several project/task pairs.
In a sense, this is a calendar view, which is fine, although I'd rather see it in list view.
Thoughts? Thank you!
Comments
-
Since you have a list of all the dates, I'd suggest using
=ANOTHERFUNCTION(COLLECT({Task Column},NOT(ISBLANK())) ...
where ANOTHERFUNCTION could be a COUNTIFS or JOIN or something else.
Craig
-
I think you've gone over my head a bit. May I pursue further?
So, if Task Columns = B through Z
and
Projects = 2 through 100
then--> under any given date listed on the separate sheet, the formula would be what? I'm not sure between COUNTIFS/JOIN, etc. And unless I'm misunderstanding your strategy would only pull from a single Task Column rather than all?
Thanks much.
-
Apologies.
What I envisioned was
SHEET 1 -- a bunch of columns associated with tasks by row.
SHEET 2 -- a column of all the dates in a year, a column of only the task name AND/OR a column of the count of the tasks for that day.
If so, the solution in a nutshell.
You'll be using X-Sheet References on two columns -- the Task Name and the Task Date.
Next, you'll collect
https://help.smartsheet.com/function/collect
COLLECT({Task Name}, {Task Date}, [Calendar Date]@row)
where those are renamed for your setup.
Then JOIN them
https://help.smartsheet.com/function/join
JOIN(COLLECT(see above), " - ")
or COUNT them
https://help.smartsheet.com/function/count
COUNT(COLLECT(see above), " - ")
To add carriage returns to the JOIN, see this thread.
https://community.smartsheet.com/discussion/feature-request-join-carriage-return-separator
I hope that clears it up.
If you have a different problem, please clarify.
Craig
Help Article Resources
Categories
Check out the Formula Handbook template!
