Posts by Kieran


    Word was not intended as a shared medium like that.
    Specifically word will prevent you from updating a document that some one else has edited.

    There are some Microsoft Sharepoint services that may get around this, but I have no experience with them.


    One quick way is to place the totals above the hedings of the column, and set the formula to sum all the rows below. eg sum(b3:b1000)
    It will then automatically include any addtional data.


    It may be possible that we are counting blanks too.
    Amend Tom's formula to =SUMPRODUCT((A4:A100<D4)*(C4:C100<D9)*(A4:A100>0)*(C4:C100>0)) and let us know the result.

    BTW - Tom's formula will be faster to execute, so it is probably better ot leave the arry formula solution alone.

    To set the graph ranges for the x axis, double click on the axis. A dialog box will appear that will allow you to set the scale. Set the minimum value to 0.3333333.


    The error is due to the manner excel stores time and decimal conversion as you seem to have guessed. Excel is not 'wrong' just too pedantic at times.

    Try =ROUNDDOWN(J17+0.0001,1) as it adds a small margin to allow for the numerical error.

    As an aside, you could have added up all of the hours without converting them to decimal. This would avoid any conversion errors in the calculation. you could then convert the totals to a decimal hour figure and reduce any net error.


    The array formula =SUM((A2:A4<E2)*(C2:C4<E3)) will work. It assumes that cell E2 contains 11:00 and E3 contains 0:31 (the registration time & max wait criteria) move them somewhere appropriate in your workbook. Remember that array formaulas must be entered using ctrl-shift-enter, not the normal enter.

    Another way to analyse the data, would be to graph the wait time against the registration time. Use the the xy scatter diagram whre the x values are the registration time and the y values are the wait time. You will very quickly see any groupings in the data.


    It is possible, but I think that you are talking system/network actions, not excel itself.
    It is possible to schedule tasks such as copy and print, however they are normally associated with system level events (as excel would need to be running to wait for the specified time).
    Some thing like or similar seems to be appropriate.


    I don;t kmow aabout the nested if functions but if you data was in cell A1 then =LEFT(A1,FIND("/",A1,1)-1) will give you the first half, and =RIGHT(A1,LEN(A1)-FIND("/",A1,1)) will give you the second half.

    Does that help?


    I normally use this to select a region to the last non blank cell.

    Sub SelectCellsC4Down()
    Range([D4], [D4].End(xlDown)).Select
    End Sub

    It has the advantage of speed, as the inbuilt routine avoids you having to loop througheach cell.


    The formula below will do what you want.

    =VLOOKUP(B2, INDIRECT(C1& "!a2:c14",TRUE),3, FALSE)

    Basically it will look in the sheet named in c1, in the area a2:c14 for the matching value in b2.

    If you put the differnt sheet names in c2 to c5 and copy the formula across it will work.
    (remember to set the corect lookup area instead of !a2:a14, and also to select the correct return column form this area, I have is curently set to 3.

    try the following


    I'll answer the first question here, I will have to think a bit about the multiple name retreival.

    Assuming the rates of pay are in range d2:d45 then the following formula will reurn the lowest non-zero rate.

    =SMALL(d2:d45, COUNTIF(d2:d45,0)+ 1)


    Could you please let us know where the 'debug error' occurs in the code and the details of the error message. It it difficult wiothout this information to offer any suggestions.


    Could you post the change event routine so that it can be reviewed.
    Also are you exiting the cell after you select the new value from the validated list? The change event is not triggered until you complete the editing of a cell - ie you move to a new cell etc.


    to find th greatest of a series of numbers use the =MAX() function.

    eg the max value of cells a1, a2, and a3 is =MAX(A1, A2, A3) or simpler =MAX(A1:A3)

    Yes it is possible to make a cell or cells to blink, however the solution will cause excel to behave in a jerky manner as the flashing is done by code.