Date input - referencing multiple sheets/sorting by week

Anonymouss

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)

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!!