Pull a calendar

JRose

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!

 

J. Craig Williams

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

JRose

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. 

J. Craig Williams

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