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!

Find more posts tagged with

Calendar View

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…