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

 

[SkipVought]

"Please explain in detail, including specific examples of data is is not sorting correctly."

Where are your EXAMPLES???

"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"

So are you selecting 1-Aug and THEN sorting? Sorting on WHAT column?

EXAMPLES! EXAMPLES! EXAMPLES! that ILLUSTRATE the problem!

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]

Forgive me, but I honestly tought my EXAMPLE was sufficient to ILLUSTRATE the problem so, as it does not appear to have been so (obvioulsy due to my inexperience with Excel), I shall try again in more detail...

On a spreadsheet titled APRIL I have a row which I shall call a header row

It has columns (which I shall refer to as fields) titled
Date | Booking Start | Booked Finish | Field | Booked Hours | Field Used | Used Hours

I have entered information in contiguous 9 rows (which I shall refer to as records) each with data all columns (the fields).

For the header row, Data | Filter | Autofilter is selected which places drop down arrows in each cell (field) of the row

Now, when I click the drop down arrow in the “header row” of the Date column (field) I am given the choice of sorting the column (field) by the following possibilities in the drop down selection list that appears (I have attempted to recreate it visually below):

Sort Ascending
Sort Descending
---------------
(All)
(Top 10)
(Custom)
1-Aug
2-Aug
9-Aug
10-Aug
22-Aug
24-Aug
(Blanks)
(NonBlanks)

When I select one of the entries (i.e. 1-Aug, 2-Aug, 9-Aug, 10-Aug, 22-Aug, 24-Aug) all rows that match the date chosen (records) are selected (including the contiguous data in the adjacent rows (records) and the others are hidden (as I would expect).

When I select either Sort Ascending or Sort Descending as opposed to any of the specific date choices (i.e. 1-Aug, 2-Aug, 9-Aug, 10-Aug, 22-Aug, 24-Aug) I would expect that all records would be organized by the choice made (Sort Ascending or Sort Descending) so that the records would then be listed in the following order (i.e. Sort Ascending):
1-Aug
2-Aug
9-Aug
10-Aug
22-Aug
24-Aug

The issue at hand however is that when I choose either Sort Ascending or Sort Descending, there is no sorting action performed on the rows (records). They remain in the (dis) ordered state prior to my selecting either Sort Ascending or Sort Descending

As an aside, I do note Geoff's comment earlier in this post...

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

Thanks again, one and all

 

[SkipVought]

Fine! You want to INSIST on EXPLAINING the problem?

Then my reply is, "when I use AutoFilter sort, it works just as advertised, with my data"

So has that helped?

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'm most humbly sorry, as it would appear my understanding of exactly what you mean by "EXAMPLE" is has upset you.

So as to avoid you further distress I shall log off now and not likely return.

 

[SkipVought]

As my PRIMARY reason for being a Tek-Tipe contributor is helping other members, and having been an Excel user for about 15 years, and having dealt with users who misterpret the symptoms, I have found that looking at actual data often clarifies the issue.

I notice this statement in your former post...

"I have entered information in contiguous 9 rows (which I shall refer to as records) each with data all columns (the fields). "

Are the COLUMNS also contiguous?
[tt]
Date | Booking Start | Booked Finish | Field | Booked Hours | Field Used | Used Hours
[/tt]
starting in column A thru column G?




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]}

 

[yogia]

Hi malagash:

I know how frustrating it can be working with computers. However, it is better not to let the situation frustrate you and look at what indeed is the problem.

So, what you are saying is that on executing the SORT command from the menu, your data is not being sorted correctly. So one can see what problem you are facing, one needs to look at an example, as Skip had stated. A meaningful example in this scenario would be before and after, as in the following example:

I realize it is not easy to effectively communicate on the forum boards -- but it does not help to get frustrated -- as at times we feel like throwing the computer out the window, but when better sense prevails we know that is not going to solve the problem.

Anyway, Skip is one of the most knowledgable and helpful persons on the Board. I am sure he was asking you for an example of what you felt was not working, like the before and after example I provided.

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

http://www.energyefficientbuild.com

 

[malagash]

Yes, they are.

I certainly am not trying to offend, just attempting to provide that which I think is needed. If I could attach a file so you could see it would obviously help however I don't beleive that is an option.

I have uploaded screen shots of the page showing and not showing the formulae which can be accessed here www.cedarsprings.cc

As to data, does this help?
Col A - date
Col B - time picked from a lookup list on the sheet "Lookups"
Col C - time picked from a lookup list on the sheet "Lookups"
Col D - location picked from a lookup list on the sheet "Lookups"
Col E - time picked from a lookup list on the sheet "Lookups"
Col F - formula
Col G - text entry
Col H - formula

I should also mention that I have the formulas entered through row 600 (in preparation for the data to be entered)

Thanks

 

[malagash]

Thanks Yogia:
I thought I had indicated that but I suppose not.

OK, the data that can be seen on the screen shot provided (which I realize is now sorted) would not sort until I highlighted all data (rows 10 - 19) and then sorted it from the Data | Sort menu.

Now, when I choose Sort Ascending from the drop down menu in the Date cell of the header row the data remains (as you can see it) sorted in a descending order.

This seems to be the part that I haven't grasped.

I am truley grateful for Skip's and your assistance, beleive me.

 

[yogia]

Hi malagash:

Thanks for posting the screen shot. Couple of Questions and comments for you ...

1) what version of EXCEL are you using?
2) from how you have shown your data layout, you may not have to put the formulas in advance
3) it is possible that as you populate the table one row at a time, EXCEL may be able to put the needed formula automatically looking at the preceding row -- but let us not get ahead of ourselves
4) do have an overall plan in mind what the project is all about
5) start with getting the most basic stuff working first, then you can keep on exteding it
6) it does take time to learn this stuff; and it takes even longer to have deeper understanding on how best to apply it
....


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

http://www.energyefficientbuild.com

 

[malagash]

Excel 2003

This is of interest but of course is foreign to me.

EXCEL may be able to put the needed formula automatically

overall plan in mind what the project is all about

The outline / purpose of the workbook is to closely monitor use of our football fields to better help plan maintenance for each field based on actual usage.

We record times that our users reserve for the football fields and also record whether the field was used or not and, if so what was the actual ending time vs. the planned or booked ending time.


As there would be a number of different fields booked during a given month it is important to be able to sort the data by the field used. We also wish to see how many hours the field is booked for vs. actual use to determine if there are any abusers of the booking privilege.

The basic stuff in my mind was to setup the appropriate columns and formulas that would give me the data I needed. In my mind I think that what is there serves the purpose. The ability to sort the data works when I make a selection from my Auto Filter drop down list but the Sort Ascending and Sort Descending function wasn't sorting from the same drop down list (and still won't) so that is how we arrived here.

As for time... you don't have to convince me, I really am in awe of the skills of people like yourself, Skip, et al and enjoy learning what I can (work and family time permitting) as I go along.

Hope this helps,
Many thanks

 

[yogia]

The basic stuff in my mind was to setup the appropriate columns and formulas that would give me the data I needed. In my mind I think that what is there serves the purpose. The ability to sort the data works when I make a selection from my Auto Filter drop down list but the Sort Ascending and Sort Descending function wasn't sorting from the same drop down list (and still won't) so that is how we arrived here.

Hi malagash:

Your screenshot shows that you have a Sort Option available via the AutoFilter command -- is that so in EXCEL 2003? I am using EXCEL 2000 and I do not have sorting option available via the AutoFilter Command.

You could have problems sorting the data if there are formula dependencies. One option would be to use AdvancedFilter to extract the data copy to another range. Then the extracted data will be values and you can sort that data in any way you like.

Chances are you will want to generate periodic (daily, weekly, whatever) reports ... in that case you might even consider using EXCEL's DataTable feature to generate such reports.

In any event, it seems you have a pretty good handle on your project now. Good Luck and keep us posted with the progress.

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

http://www.energyefficientbuild.com

 

[malagash]

Thanks.
I shall research your commment re: Formula dependancies and EXCEL's DataTable feature and keep you posted.
Thanks to you and all