Forum Discussion
Populate List Based on Date and Crew Number
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.- Thanks again for your response - I was able to get it working correctly. I ended up with the following formula:
=IFERROR(INDEX('Task List'!$A2:$F2,MATCH(1,(('Task List'!$E2:$E$100=TODAY()+1)+('Task List'!$E2:$E$100=TODAY()))*(('Task List'!$F2:$F$100="All")+('Task List'!$F2:$F$100=1)),0),1),"??")
This pulls all tasks for the crew for today and tomorrow's date.