Book Library with Form

NikkiOno

We have several books in our store that we lend out for employees to read and I need a way to easily keep track of requests and know if they are in use or available. Below are screenshots of what I am thinking but I am unable to figure out what type of formula I will need to know when a submission is made to request a book, if it is available or in use.

Has anyone ever done something like this before?

Paul Newcome

You would use something more like this:

=IF(COUNTIFS([BOOK TO BORROW]:[BOOK TO BORROW], @cell = [BOOK TO BORROW], [DATE PICKED UP]:[DATE PICKED UP], @cell <> "")> COUNTIFS([BOOK TO BORROW]:[BOOK TO BORROW], @cell = [BOOK TO BORROW], [RECEIVED BACK TO STORE]:[RECEIVED BACK TO STORE], @cell <> ""), "Unavailable", "Available")

NikkiOno

I WOULD LOVE FOR THIS ENTIRE PROCESS TO BE AUTOMATED AND THIS IS HOW I AM THINKING IT COULD WORK.

BOOK LIBRARY IS ONE SHEET

BOOKS BORROWED IS ANOTHER SHEET - FORM

TO BORROW A BOOK, NEED TO FILL OUT FORM

IF BOOK IS AVAILABLE, APPROVED AND GIVEN DUE DATE OF 2 WEEKS. THIS SHEET WOULD NEED TO CHECK THE STATUS ON THE BOOK LIBRARY SHEET TO SEE IF ITS AVAILABLE OR IN USE

ONCE BOOK IS RETURNED, ON DUE DATE AN EMAIL WILL GO OUT TO UPDATE STATUS OF BOOK "RETURNED OR NEED MORE TIME"

IF BOOK IS IN USE, EMAIL TO GO OUT LETTING THEM KNOW WHEN THE BOOK WILL BE AVAILABLE. 

Paul Newcome

You would basically COUNTIFS how many dates have been submitted for "Borrowed" and compare that to the COUNTIFS of how many dates have been submitted for "Returned". If "Borrowed" is more than "Returned" then the book is out.

NikkiOno

@Paul Newcome i need a little more help. what column would the countifs go in? i am not sure i understand.

NikkiOno

@Paul Newcome

THIS IS THE FORMULA I AM TRYING

=COUNTIFS([BOOK TO BORROW]:[BOOK TO BORROW], "ATOMIC HABITS", [DATE PICKED UP]:[DATE PICKED UP], " "). ITS NOT WORKING THOUGH.

Paul Newcome

You would use something more like this:

=IF(COUNTIFS([BOOK TO BORROW]:[BOOK TO BORROW], @cell = [BOOK TO BORROW], [DATE PICKED UP]:[DATE PICKED UP], @cell <> "")> COUNTIFS([BOOK TO BORROW]:[BOOK TO BORROW], @cell = [BOOK TO BORROW], [RECEIVED BACK TO STORE]:[RECEIVED BACK TO STORE], @cell <> ""), "Unavailable", "Available")

NikkiOno

@Paul Newcome Thank you!

Paul Newcome

Happy to help. 👍️

NikkiOno

@Paul Newcome Thank you!