Date input - referencing multiple sheets/sorting by week

Hi,

I'm creating master task sheets for each project/event, then other task sheets for each person with just their individual tasks. I have made it so when tasks are added to the master sheets, assigned to a particular person it will get copied to their sheet. I created a formula to automatically match/update the end date value on the person's task sheet when it is changed on either of the master sheets. I used this formula to update the dates from both sheets and it worked..

=IF(Source@row = "Weekly Mtg Tasks", INDEX({WeeklyMeetingEndDate}, MATCH([Task Name]@row, {WeeklyMeetingTaskName}, 0)), IF(Source@row = "project plan", INDEX({ProjectPlanEndDate}, MATCH([Task Name]@row, {ProjectPlanTaskName}, 0))))

I then was trying to create headings in the personal task sheets to categorize the tasks by week, and have a formula to input the end date on the 'week heading' row so I wouldn't have to move the headings manually each time i sort by end date, is there a way to do this? I added the week dates manually to this sheet, (its not pulling from one of the master sheets), but since the values for end date are a column formula it wont allow me to edit the value manually. I tried adding this formula to the end of the other one, but it gave me an error..

=IF(ISBLANK([End Date]@row), MID([Task Name]@row, FIND("-", [Task Name]@row) - 8, 7))

Any suggestions on how I could get this to work? (the below picture is with only the first formula put in the end date column)

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hey @Anonymouss

This is great! And exactly what I would do. Can you clarify what error you're getting? This worked for me in my sheet. Is it possible a parentheses is out of place?

Another option would be to base it off of your source being "other":

=IF(Source@row = "Other", MID([Task Name]@row, FIND("-", [Task Name]@row) - 8, 7), IF(Source@row = "Weekly Mtg Tasks", INDEX({WeeklyMeetingEndDate}, MATCH([Task Name]@row, {WeeklyMeetingTaskName}, 0)), IF(Source@row = "project plan", INDEX({ProjectPlanEndDate}, MATCH([Task Name]@row, {ProjectPlanTaskName}, 0))))

You'll get an "invalid value" error if the formula can't find the "-". You could try wrapping an IFERROR around your MID formula:

=IF(Source@row = "Other", IFERROR(MID([Task Name]@row, FIND("-", [Task Name]@row) - 8, 7), ""), IF(Source@row = "Weekly Mtg Tasks", INDEX({WeeklyMeetingEndDate}, MATCH([Task Name]@row, {WeeklyMeetingTaskName}, 0)), IF(Source@row = "project plan", INDEX({ProjectPlanEndDate}, MATCH([Task Name]@row, {ProjectPlanTaskName}, 0))))

Let me know if you were able to get this working!

Cheers,

Genevieve

Anonymouss

Hey Genevieve,

Yes, I had gotten it to finally work the other day, but thanks for your response!

I think it was just that a parentheses was out of place.. below is the formula that I used..

=IF(Source@row = "Department Tasks", INDEX({DepartmentEndDate}, MATCH([Task Name]@row, {DepartmentTaskName}, 0)), IF(Source@row = "Q1 Sprint Tasks", INDEX({Q1SprintEndDate}, MATCH([Task Name]@row, {Q1SprintTaskName}, 0)), IF(Source@row = "Recurring Task", INDEX({RecurringTaskEndDate}, MATCH([Task Name]@row, {RecurringTaskName}, 0)), IF(Source@row = "Other", MID([Task Name]@row, 5, 9)))))

I then was having trouble sorting by end date, it would sort by date, but just put all the headings at the bottom.. I think its because it wasn't recognizing the date pulled from the task name as a date value..

I created another column, "Date Sorter" and put this formula in to make all the values a date value, then sorted by end date in that column, but is there another way around this so I don't have to have the extra "date sorter" column?

=DATE(VALUE("24" + RIGHT([End Date]@row, 2)), VALUE(LEFT([End Date]@row, 2)), VALUE(MID([End Date]@row, 4, 2)))

Thanks again for your help!!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.