Forum Discussion
Solved
Populate List Based on Date and Crew Number
Hi,
I am creating a task list for a department of 4 crews. Each crew has their own task list sheet, and that sheet pulls tasks from a main "Task List" sheet based on a =today() function and the crew number. The "Task List" sheet also acts as a place for management to deposit new tasks. Crews will share daily tasks, but many tasks will be crew-specific.
So, for example, Crew 1 will open their sheet and it will show their tasks that are due today and tomorrow.
I've tried different "index/match" functions, "xlookup", etc. but I can't get anything to populate the lists correctly. Anyone have any insight on which way to proceed with this?
Screenshots below:
Task List sheetCrew 1 sheet. Yellow cells will be completed by operators.
It sounds like you are on the right track using functions like INDEX/MATCH or XLOOKUP to pull tasks from the main "Task List" sheet to each crew's task list sheet based on crew number and due date. Here's a possible approach you could try:
- In each crew's task list sheet, create a table with columns for the task name, due date, and crew number.
- In the "Task List" sheet, add a column for crew number and fill in the crew number for each task.
- Use the INDEX/MATCH or XLOOKUP function to pull tasks from the "Task List" sheet to each crew's task list sheet based on the crew number and due date. For example, you could use a formula like this to pull tasks for Crew 1 that are due today:
=IFERROR(INDEX('Task List'!$A$2:$C$100,MATCH(1,('Task List'!$B$2:$B$100=TODAY())*('Task List'!$C$2:$C$100=1),0),1),"")
This formula uses the MATCH function to find the row in the "Task List" sheet where the due date matches today's date and the crew number matches 1 (for Crew 1). It then uses the INDEX function to pull the task name from column A of that row. If no match is found, the formula returns an empty string.
- Repeat step 3 for each crew and each day.
Note that this approach assumes that each task has a single due date and crew number. If tasks can have multiple due dates or be assigned to multiple crews, you may need to modify the approach accordingly.
I hope this helps!
It sounds like you are on the right track using functions like INDEX/MATCH or XLOOKUP to pull tasks from the main "Task List" sheet to each crew's task list sheet based on crew number and due date. Here's a possible approach you could try:
- In each crew's task list sheet, create a table with columns for the task name, due date, and crew number.
- In the "Task List" sheet, add a column for crew number and fill in the crew number for each task.
- Use the INDEX/MATCH or XLOOKUP function to pull tasks from the "Task List" sheet to each crew's task list sheet based on the crew number and due date. For example, you could use a formula like this to pull tasks for Crew 1 that are due today:
=IFERROR(INDEX('Task List'!$A$2:$C$100,MATCH(1,('Task List'!$B$2:$B$100=TODAY())*('Task List'!$C$2:$C$100=1),0),1),"")
This formula uses the MATCH function to find the row in the "Task List" sheet where the due date matches today's date and the crew number matches 1 (for Crew 1). It then uses the INDEX function to pull the task name from column A of that row. If no match is found, the formula returns an empty string.
- Repeat step 3 for each crew and each day.
Note that this approach assumes that each task has a single due date and crew number. If tasks can have multiple due dates or be assigned to multiple crews, you may need to modify the approach accordingly.
I hope this helps!
Thanks for your response! Looks like we are both on the same page with the Index/Match approach, but I may be confusing myself on the syntax of the formula. I modified your formula to this:
=IFERROR(INDEX('Task List'!$A$2:$E$100,MATCH(1,('Task List'!$E$2:$E$100=TODAY())*('Task List'!$F$2:$F$100=1),0),1),"??")
But I'm still getting an #N/A error. When stepping through the formula, it seems to fail at the MATCH portion of the formula. It solves the "E2:E100=TODAY()" portion, solves the "F2:F100=1" portion, returns TRUE*TRUE, then MATCH(1,1,0), which turns into #N/A with the message "A function in this formula causes the result to change each time the spreadsheet is calculated. The final evaluation step will match the result in the cell, but interim steps may not." I'm guessing this is due to the TODAY() function but I'm not entirely sure.
Any thoughts? Did I misinterpret the way the formula was written?
Thanks again!
- The error message suggests that the MATCH function is not finding a match for the given criteria.
One possible issue with the formula is that it is referencing column F in the MATCH function, but the INDEX function only specifies a range up to column E. This could cause an error if there is no data in column F.
Another possible issue is that the MATCH function is using an array formula, which needs to be entered as an array formula in order for it to work correctly. To enter an array formula, you need to press Ctrl+Shift+Enter instead of just Enter after typing the formula. This will enclose the formula in braces {} to indicate that it is an array formula.
You could try adjusting the column references in the formula and making sure to enter it as an array formula to see if that resolves the issue.
