Posts by Barryj

    I am trying to write a formula that will recgonise either text or numicial value as the result is used with a match formula.


    In column C I have data as follows:


    1400 SBY
    1230 9985
    ADO


    I am using a =--RIGHT(C4,4) formula in column E to get the required data and then using my match formula to extract other data.


    How can I rewrite the above formula so that it can read either text or numbers that will allow my match formula to work.

    Re: Time calculation


    Thankyou dave for the formula, it works great, I did do a bit of research before posting for this problem and did not find anything that I could adapt.


    Thanks again for all the assistance.

    I am trying to add the total time and actual time of some employees each fortnight, some of the cells will only contain a - and others will contain their hours across the 2 week period.


    Can anyone help me with a formula that will add up these times.


    When I started with just adding the relevent cells, after adding 2 cells it did'nt work, hope this makes some sense.


    I have included a attachment that hopfully will help.


    Thanks.

    I have formated my cells as custom hh:mm and want to have a 24 hour clock calculate time.


    In cell D6 I have starting time of 23:00
    In cell E6 I have finish time of 07:00


    This should equal 7:00 hours but I get ######### am I doing somthing wrong or do I have to format the cells differently.


    The formula in F6 is E6-D6.

    Re: Hide cells with Macro not working properly.


    I tried this but it still effects the original workbook sheet even though I have specified the for this to happen on the workbook where the data is pasted.


    [Code]Sub Copy()
    Dim RngCopy As Range
    Dim RngPaste As Range

    Set RngCopy = Workbooks("Parts.xls").Sheets("Spare Parts").Range("A1:I67")
    Set RngPaste = Workbooks("Parts Email.xls").Sheets("Spare Parts").Range("A1:I67")
    RngCopy.Copy RngPaste
    With Workbooks("Spare Parts Email.xls").Sheets("Handicapps")
    Range("D9:D67").Select
    Selection.Font.ColorIndex = 2
    Range("I9:I67").Select
    Selection.Font.ColorIndex = 2
    End With
    End Sub[Code]

    I have this code which is working fine coping from one workbook to another, but on the sheet where the data is posted I want to hide or delete cells D9:D67 & F9:F67.


    I recorded some code to alter my original code but it also hides the data on the sheet where the data is copied from, any ideas how to fix this?


    This is what I had


    Code
    Sub Copy()
        Dim RngCopy As Range
        Dim RngPaste As Range
        Set RngCopy = Workbooks("Parts.xls").Sheets("Spare Parts").Range("A1:H67")
        Set RngPaste = Workbooks("Parts Email.xls").Sheets("Spare Parts").Range("A1:H67")
      End Sub


    This is what I changed it to


    Re: Formula for matching and moving contents


    Your formula works fine in the ranking and showing the best result for each colum, but its not quite what I have in mind, have included another workbook with some formula in how I think it should work.


    But because I dont know how to include more than 2 arguments in these formulas this is where I am getting stuck.


    Hope this helps explain things a little better. :?

    Re: Formula for matching and moving contents


    [QUOTE=Joebbshop]I think I understand basically what you want. See attached.


    The main difficulty is in not giving a rank in the Nett competition to the winner of the Gross competition and to not leave a hole where their rank would be. To accomplish, I used this formula:


    Code
    From cell F2
    
    
    =IF(D2=1,"",IF(RANK(E2,E$2:E$10)>MATCH(MAX(C$2:C$10),C$2:C$10,0),RANK(E2,E$2:E$10)-1,RANK(E2,E$2:E$10)))


    First part doesn't give a Nett rank to the winner of the Gross.


    Rest of formula checks to see what Nett ranking the winner of the Gross competion would have had. If the ranking of the current cell is greater than the ranking of the Gross winner, subtract 1 to close the hole.


    Makes sense?


    Alex.

    I Have a list of names that are a result of a golf competition, they are divided into 2 columns, one their Gross score & their Nett score.


    What I want to do is determine the winner of the Nett competition, a person can only claim one trophy so this is where it gets complicated.


    In list 1 the Gross winner could also be the Nett winner but as they can only claim 1 trophy they will take the Gross Trophy.


    this is what should happen, IF H8=E8,E9,H8 and so on so that if the same name appears in the same position on both lists then the next name is elevated.


    Hope that someone can understand this and help? :?

    Re: Trigger Macro On Another Sheet


    Ok, have tried that and it works quite well on 1 sheet but multiple sheets is quite slow, plan B, I recorded this macro that I would have hoped would sort the 4 sheets, but I am getting a 1004 run time error on line 3 of the first part of the Macro.

    Code
    Range("A2", Range("F31").End(xlUp).Address).Select


    Can anyone either point me in the right direction or modify the code to run on these 4 sheets.




    Re: Activate sheet to run macro in another workbook


    Sorry, The macro is currently in a workbook named golf file, I want to put it in a new workbook called results and when the results workbook is activated the macro will run and search through the workbook golf file and retreive the results as it currently does.


    Sorry about the confusion. Thanks for the help. :)

    I have the Macro below which currently runs in a workbook called Golf File,
    I want to have this macro in a seperate workbook as it produces results, and is only required at the very end of the season, how can I modiify it so that when I activate the sheet in the new workbook that it will open and run the macro through the workbook Golf File.



    I hope this makes some sense, and thanks for any help :?

    I was hoping someone could show me how I could get the first macro to trigger the second macro, currently the second macro is a sheet activate macro.


    I want to get it to run each time the first macro runs so that I can hide the second sheet as it only sorts data that is linked to a third sheet which shows the results.


    Thanks inadvance for any help you guys can provide. :thanx:




    Macro on second sheet