Data entry for tracking items

Royce.

I have an automatically populated table that shows any outstanding item, this includes IDs, date info etc. This will be overwritten daily as items are added/removed by our internal system.

I'd like to do the following: Add additional columns that calculate if items a re past due and owners by looking up the ID with another pre-populated table. Manually add information that shows if the item can be ignored in the future (if the system doesn't remove it for some reason)

Ideally, I'd end up with one table showing the outstanding items where the user can scroll through these and add info one by one.

Genevieve P.

Hi @Royce.

This all sounds possible! 🙂 Here's what I would do:

1) Past Due

Have a Column Formula looking at the Date column to check it against a specific date. If it's past due, return the text "Past Due" and then use Conditional Formatting to highlight that cell or the row.

Here's an example of what that formula might look like, if "Past Due" means the Date cell is in the past compared to Today:

=IF([Date Column]@row < TODAY(), "Past Due", IF([Date Column]@row = TODAY(), "Due Today", ""))

2) Assigned To

For the Assigned To column, if you have a separate reference sheet with everyone's Contact and the ID of the Task, you can use an INDEX(MATCH formula to bring back the Contact based on that ID.

Here's more information: Formula combinations for cross sheet references

=INDEX({Assigned To Column reference sheet}, MATCH([ID Column]@row, {ID Column reference sheet}, 0))

3) Reference Past Due

Once you have this data in the same sheet, I would suggest setting up a Report that shows each person their individual Assigned To rows that are filtered by Past Due.

See: Create filter criteria to control data in Report Builder

Cheers!

Genevieve