Using Excel to log hired cars

To attach a workbook:

Click on GO ADVANCED and then scroll down to[COLORT="#FF0000"] Manage Attachments[/COLOR] to open the upload window (or use the paperclip icon).

If you are willing to go the Access route, then here is a schema for tracking Car Hires

http://www.databaseanswers.org/data_...hire/index.htm

Here is a bit of an example that may get you started.

Suppose you own a fleet of cars with licenses (or other unique ID) such as: AAA, BBB, CCC, etc.

You put them on the schedule as indicated.

Next you enter in the start and end date of the new hire in cells B1 and B2.

I have some helper cells that find where these dates occur on row 1. These are used with the offset command to define a named dynamic range:

Car_Range =OFFSET(Sheet1!$F$1,1,Sheet1!$C$1-1,100,Sheet1!$C$3+1)

The offset command has 5 arguments:
Start cell
Number of cells to go down
Number of cells to go right
Number of rows to return
Number of columns to return

So we start in cell F1 (First date in the range)
Go down one row (so we are now in cell F2)
Go over the number of cells in cell C1 (5 cells to cell J1)
Return a range 100 rows deep (since you have a maximum of 100 cars)
And a number of columns = the number of cells in cell C3 (5) + 2.

This gives is the range J2:O101. This range will change when you change the start and end dates.

This article explains more about named ranges and offset: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges

Then in Columns A there is a list of licenses and in column B and indicator of how many days within the range, those licenses are in use. If it’s zero, then the car is available for the period. I threw in a bit of conditional formatting to show green when a car is available.

Hi and welcome to the forum.

You really need to upload your workbook so that we can see the request in context.

Personally I think this is probably a task for a Pivot Table. List all your cars with the start and end date of hire in two columns, and another column that has the

Formula:

Please Login or Register  to view this content.

The use a Pivot Table and a Slicer to filter the PT using the field header that contains the "Available" cars.