requesting an assist in spreadsheet solution 1

[malagash]

I monitor bookings and use for 17 local sports fields to plan maintenance schedules but to date have only done so using paper and pencil. I have minimal experience with Excel and hope that there might be a relatively easy spreadsheet solution to my situation.

I need to monitor bookings as well as use because not everything booked results in a field being used (for various reasons) which affects my maintenance scheduling.

What I visualize being able to do is enter bookings as they are made then follow up later confirming that the booked time was either used or not. I then wish to be able to look up at any given point, the total hours used for any field (to determine maintenance requirements) and compare that figure to the total hours booked for that field (to monitor reasons for non use).

I would welcome any suggestions as to where I might start.
Thanks

 

[Hasit]

I would base each field on the month you are working on. So set up each field with 31 cells for each day of the month.

Column A is the field name

Column B are the dates and entering something here would mean that the field has been booked (I have assumed that you can only book a field out once a day)

Column C is how long the field was used for in hours

Column D would have one an if statement. Something like IF SUM OF TOTAL HOURS IN COLUMN C FOR FIELD A IS GREATER THAN 30 HOURS, THEN MAINTENANCE REQUIRED

 

[yogia]

Hi malagash:

So, you are going to set up an EXCEL spreadsheet. I suggest that you set up the spreadsheet as an EXCEL List/Database. Don't let the report(s) that you want to generate dictate your layout of the spreadsheet.

If your data is laid out according to the database rules, you would be able to generate whatever report(s) you need now, or you might need to generate in the future.

So, get started and start EXCELing!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[malagash]

Thanks Yogi but database rules are over my head at the moment. Can you suggest a starters guide to building a db in Excel?
Thx

 

[xlbo]

you do NOT build a db in excel - you build a flat file in excel that is similar to a database table

The best way to do this is to repeat data such that anything you need to report on occurs on every line. A starter for 10 might be:

Field Name / Date / From / Till / Booked Flag / Used Flag

The Field Name should repeat for each booking, for each date - if you set up in this kind of format, you should be able to do any reporting you require

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[malagash]

Thanks, I will have a go at it and get back this afternoon

 

[malagash]

Thanks everyone. I have made a rough start with your suggestions and I think they will work just fine.
Set up is:
col A = Field - validation drop down list
col B = Date - validation drop down list
col C = Start Time - validation drop down list
col D = End Time - validation drop down list
col E = Total Hours Booked - sum of cols C & D
col F = Used Y/N? - text entry
col G = Total Hours Used - if statement that inserts booked hours based on input of a "y" in col F

I have but one question:
The End Time chosen from the list must be later than the Start Time -- is there a way to prevent a user from selecting an End Time that is the same as or earlier than the Start Time?

I would upload the sample file but do not see how it is possible in the forum.
Thanks once again

 

[SkipVought]

"is there a way to prevent a user from selecting an End Time that is the same as or earlier than the Start Time?"

Data > Validation
use a formula to assure that the value will be greater than or equal to start time.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[malagash]

Thanks Skip but I am using Validation to create the list of hours. My inexperience doesn't allow me to determine where I can use List and validate the choice from within that list or if it is a custom entry, I cannot visulize the formula.

 

[xlbo]

If you are happy for the user to enter the date manually rather than picking form a list, you may use a custom validation such as:

=AND(NOT(ISNA(VLOOKUP(EndTime,ListOfTimes,1,FALSE))),NOT(EndTime<StartTime))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[malagash]

My concern is that other users in the office would end up contacting me when the time doesn't display properly because they didn't enter the :
I am not always there and was hoping for something relatively fool proof.
Maybe I'm the fool for hoping so...

 

[xlbo]

If you need to use a list AND check for a valid time (after start time) you will require VBA code utilising the CHANGE event of the worksheet. If you are comfortable with this, please post in the VBA Forum: Forum70

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[malagash]

Thanks Geoff, I have no experience with VBA but am not above trying to learn.
It might be best for all concerned for me to read some basic tutorial(s) about it first before venturing into a forum as a complete newbie

 

[xlbo]

apologies - missed the 7 off the end. VBA forum is forum707

Feel free to venture into the forum as a newbie - if you are willing to read and learn, you will get all the support you need.

Just had a thought though - you may be able to dynamically change the list of times available to the 2nd data validation list based on the 1st

If you have a list of times as the validation list, NAME this list sr_Times. For the purposes of this example, this list would be in cells A1:A20 in a sheet called "Lookups" - the list should be sorted "ascending". Cell A1 should be named "sr_Start"

You can then use this formula as a List entry for the 2nd data validation dropdown:

=OFFSET(sr_Start,COUNTIF(sr_Times,"<=" & C2),,COUNTA(sr_Times)-COUNTIF(sr_Times,"<=" & C2),1)

where your 1st data validation entries are in C2 & D2

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[malagash]

Fabulous, thanks Geoff

 

[malagash]

One more question...
I have a total for the col "Used Hours" which gives me a total for all fields. If I filter the data by "Field Booked" can a formula total only the records of the filtered data?

 

[xlbo]

the SUBTOTAL formula works only on visible rows - should do what you want

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[malagash]

This is going great and will be of tremendous assistance.

I have the Auto Filter working for each column but notice that Sort Assending and Sort Decending do not work on the column.

Any thoughts?
Many thanks

 

[SkipVought]

What do you mean by sort not working? Please explain in detail, including specific examples of data is is not sorting correctly.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[malagash]

I have 9 rows (records) each with data in 8 columns (fields). With Data | Autofilter selected I click the drop down arrow in a column title in which I am given the choice of sorting by

Sort Ascending
Sort Descending
---------------
(All)
(Top 10)
(Custom)
1-Aug
2-Aug
... all of the other possibilties in the column
(Blanks)
(NonBlanks)

The columns to the right are contiguous and all contain data

If I select one of the entries (i.e. 1-Aug) all 1-Aug records are selected.

When I select either Sort Ascending or Sort Descending I owuld expect that all records would be organized by the appropriate however, there is no sorting action performed on the list/records

Thanks