# Problem making a spreadsheet schedule based on the availability of two items.

1. ## Problem making a spreadsheet schedule based on the availability of two items.

For some reason, the logic behind the spreadsheet schedule I am making is very confusing to translate to the spreadsheet. I am going to be servicing computers for about a hundred users, while their computers are being serviced, they will be borrowing one of two loaner computers. The normal stretch for servicing the computers is three days, but it could be shorter or longer. The two loaner computers get loaned out based on what loaner computer is available when that user brings in their computer.

I am making a schedule that it like the following:

Col A: User Name
Col B: Date user is to bring in their computer
Col C: Date user is intended to pick up their computer
Col D: What loaner computer in to be given to that user to use in the meantime (Loaner 1 or Loaner 2)

Say the list look like this

Row 1: Johnny Apple, 05/01/2019, 05/03/2019, Loaner 1
Row 2: Kevin Bicycle, 05/01/2019, 05/03/2019, Loaner 2
Row 3: Larry Cat, 05/04/2019, 05/07/2019, Loaner 1
Row 4: Marvin Dot, 05/04/2019, 05/07/2019, Loaner 2
Row 5: Nancy Egg, 05/08/2019, 05/11/2019, Loaner 1
Row 6: Ollie Fawn, 05/08/2019, 05/11/2019, Loaner 2
Row 7: Pat Green, 05/12/2019, 05/15/2019, Loaner 1
Row 8: Randy Holly, 05/12/2019, 05/15/2019, Loaner 2

Now, without any needed alterations to the schedule, the schedule seems obvious and predictable. However, I have to allow for a situation like, say, Larry Cat's computer actually ends up taking 7 days to service, so the full three days are extended by four. That means that the following alternation to the schedule would automatically recalculate to:

Row 1: Johnny Apple, 05/01/2019, 05/03/2019, Loaner 1
Row 2: Kevin Bicycle, 05/01/2019, 05/03/2019, Loaner 2
Row 3: Larry Cat, 05/04/2019, 05/11/2019, Loaner 1
Row 4: Marvin Dot, 05/04/2019, 05/07/2019, Loaner 2
Row 5: Nancy Egg, 05/08/2019, 05/11/2019, Loaner 2
Row 6: Ollie Fawn, 05/12/2019, 05/15/2019, Loaner 1
Row 7: Pat Green, 05/12/2019, 05/15/2019, Loaner 2
Row 8: Randy Holly, 05/16/2019, 05/19/2019, Loaner 1

Can someone help this novice work out this logic is Excel? I cannot seem to work out how I would get a spreadsheet to work out this logic?

Timothy

2. ## Re: Problem making a spreadsheet schedule based on the availability of two items.

Hello timothysklugh and Welcome to Excel Forum.
Perhaps the following will help.
The first two dates when the client is expected to bring their computer in are manually filled then the following formula fills rows 4 and down:
Formula:
`Please Login or Register  to view this content.`

The date on which the computer is expected to go back out to the client is filled using: =MIN(D2,SUM(B2,3))
The actual dates on which the computer goes back out to the client need to be manually filled.
The first two rows of loaner computer need to be manually filled then, starting with row 4, use: =IFERROR(INDEX(E\$2:E3,MATCH(ROWS(A\$1:A1),F\$2:F3,0)),"")
The Rank column, which may be moved and/or hidden for aesthetic purposes uses:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

3. ## Re: Problem making a spreadsheet schedule based on the availability of two items.

Thank you for responding! I will take a look.

Timothy

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1