I have a sheet for managing x number of assets which are going on loan to customers. The attached is a concept and non actual data.
I want to build a system to filter/show all loans which are overdue and then a function to contact these users by email
This is the approach I have taken;
I have generated data including simulated dates for out/return
I have added a column "returned" - (Yes or No.)
I added a column "late" which returns true/false based on the date being >today.
I added a pivot table in a second sheet - this currently returns all instances where the return date is in the past.
I have three questions;
If the "returned" column (F) is "Yes" - then the asset has been returned and the Late column should show "false". I assume I need to nest IF functions to achieve this, but I do not know the correct syntax.
Is it possible to automate an email to the users generated by the pivot table? (imagine there were usernames captured and not "person 1").
The email would send to the names given, and the body would be the same apart from a reference to the asset in question which would be dynamic. Is this kind of thing possible in MS excel?
If you want me to explain anything in more detail please let me know.
Bookmarks