Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Hello Simone,

Hope you enjoyed your Microsoft Excel Intermediate course with Best STL.

Thank you for your question regarding working with date and time calculations.

I'm surprised that your helpdesk system does not provide you with information on breaching the SLA. Please remember, the Service Level Agreement usually differs according to the type of incident reported... some having only 4 hours, others 8 hours and still others with longer SLA times. I don't know how the SLA was set up in your organisation so I cannot say how best you can monitor the results from Supportworks within Excel.

I have created a small spreadsheet with a few calculations using typical support incidents. Working with dates and time can be tricky. Excel does a few strange things with time. If you subtract one date/time cell from another cell containing date/time, the result will be a number. You then have to convert the format of the cell to Time.

I have added an IF function in the last column and..... [post continues]

Hello Laura,

Hope you enjoyed your Microsoft Advanced course with Best STL.

Thank you for your question regarding adding the scenario button onto your toolbar.

First of all, right click anywhere on your toolbar, then click on Customise. Select the Commands tab, and click on the Tools category which you will find on the left hand side. On the right hand side Commans section, scroll down until you get to Scenario: (has a drop-down arrow) then drag this item onto your toolbar next to your help button. You will see a black I bar, then let your mouse go. Click close on the Customise dialog box.

This action will place the scenario drop-down on your standard toolbar. Have fun with it!

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

Hi Robert

Thanks for your question

In order to resolve this it would be useful if I could see a copy of the relevant code. Could you please post it to this thread?

Thanks

Stephen

Hi Stephen,

I've pasted the functions below. The first one was throwing up errors when the form was first loaded. It doesn't of course fire at startup as I might have inferred but when clicking on the list. This went away when I opened the VBA editor as described in my initial post. Now the problem has gone completely!

So you can make sense of it, there are three lists: GroupList; GroupSectionList; PersonList. The query that populates GroupSectionList has the value of GroupList as a selection criteria. PersonList does the same with GroupSectionList.

The intention of the VBA is that when someone clicks on GroupList, the related Sections are displayed in GroupSectionList and also the members of PersonList that relate to the first record in GroupSectionList. This works only partially because SetFocus doesn't seem to do the same as physically clicking on the list and therefore the second function doesn't fire until GroupSectionList is clicked on. This is probably something for ano..... [post continues]

Hello Laura,

Hope you enjoyed your Microsoft Excel Advanced course with Best STL.

Thank you for your question regarding grouping dates in a PivotTable using imported data.

I have created a PivotTable using data from an Access database with a date field included. I placed the date field into the Rows section. I then selected all dates for a particular month... e.g. January 2010, then I right-clicked on the selection, navigated to Group and Show Detail, then I clicked on Group. This gave me a generic name which I changed to Jan 2010. I then double-clicked the new group name which hides the detail.

It works for me and the data I am using comes from an Access database so I'm not sure why you are not able perform the same action.

It may be easier if you go to the Data menu, Import External Data and select New Database Query. This will open the Create New Data Source dialog box. In the first box type the Name for your database. In the second box, click the drop-down arrow and ..... [post continues]

Hello Laura,

Hope you enjoyed your Microsoft Excel Advanced course with Best STL.

Thank you for your question regarding grouping dates in a PivotTable using imported data.

I have created a PivotTable using data from an Access database with a date field included. I placed the date field into the Rows section. I then selected all dates for a particular month... e.g. January 2010, then I right-clicked on the selection, navigated to Group and Show Detail, then I clicked on Group. This gave me a generic name which I changed to Jan 2010. I then double-clicked the new group name which hides the detail.

It works for me and the data I am using comes from an Access database so I'm not sure why you are not able perform the same action.

It may be easier if you go to the Data menu, Import External Data and select New Database Query. This will open the Create New Data Source dialog box. In the first box type the Name for your database. In the second box, click the drop-down arrow and ..... [post continues]

How do you use subtotal with product

I have refined my code and made it populate the two subordinate lists as I mentioned I was trying to do in my previous post.

I spent ages going down a blind alley trying to use sendkey to do a left click. Setting the default value achieved the same thing. My new code is here:

Private Sub GroupList_Click()
Me.GroupSectionList.Requery
Me.GroupSectionList.DefaultValue = Me.GroupSectionList.Column(0, 0)
Call GroupSectionList_Click
End Sub

Private Sub GroupSectionList_Click()
Me.PersonList.Requery
Me.PersonList.DefaultValue = Me.PersonList.Column(0, 0)
End Sub

When someone clicks on a value in GroupList, the other two lists are queried using related values.

Hello,

Is there a way of moving from activecells without using the offset function?

Regards

Hi John,

It works as you expect:

=PRODUCT(SUBTOTAL(2,$D$15:$D$2169),SUBTOTAL(1,$D$15:$D$2169))

will multiply the count of a field by the average of a field of a filtered database - effectively giving the answer you would have achieved with

=SUBTOTAL(9,$D$15:$D$2169)

It would also work across 2 different databases, if you were applying filters to both. Remembering where you were with each filter might be tricky though...

Hope this answers the question!

Clare Glover

How can you print out the relationship diagram displayed in split screen ?

How to print the output of this view.

How is a second year of support covered? Just by using it the first year?