Posts by Red Rooster

    Re: Sliding Scale/Thresholds


    Pike is right the total should be 1882.


    I've attached an alternative which will allow you to change the pay scales, and not have to alter the formulae.

    Files

    • Scale.xlsx

      (8.23 kB, downloaded 63 times, last: )

    Re: Time format hh:mm:ss.000


    In b1 input =Rounddown(a1,0), this extracts the date segment, then =a1-b1 will give you the time segment.[TABLE="width: 83"]

    [tr]


    [TD="class: xl63, width: 111, bgcolor: transparent, align: right"][/TD]

    [/tr]


    [/TABLE]

    Re: TO many arguments


    You have to delete the ) from here


    =IF(D4="","",IF(AND(Data!BO4="TRUE",Data!H4=7),VLOOKUP(Data!$D4,'Program Codes'!$A:$AH,MATCH(Data!$E4,'Program Codes'!$A$1:$AH$1,0),FALSE)-'Base Price Charges - Credits'!Q4+'Base Price Charges - Credits'!R4-'Base Price Charges - Credits'!$U$2),IF(AND(Data!BO4="TRUE",Data!H4<7),VLOOKUP(Data!$D4,'Program Codes'!$A:$AH,MATCH(Data!$E4,'Program Codes'!$A$1:$AH$1,0),FALSE)-'Base Price Charges - Credits'!Q4-'Base Price Charges - Credits'!$U$3,0))


    So it look like this
    =IF(D4="","",IF(AND(Data!BO4="TRUE",Data!H4=7),VLOOKUP(Data!$D4,'Program Codes'!$A:$AH,MATCH(Data!$E4,'Program Codes'!$A$1:$AH$1,0),FALSE)-'Base Price Charges - Credits'!Q4+'Base Price Charges - Credits'!R4-'Base Price Charges - Credits'!$U$2,IF(AND(Data!BO4="TRUE",Data!H4<7),VLOOKUP(Data!$D4,'Program Codes'!$A:$AH,MATCH(Data!$E4,'Program Codes'!$A$1:$AH$1,0),FALSE)-'Base Price Charges - Credits'!Q4-'Base Price Charges - Credits'!$U$3,0)))

    Re: TO many arguments


    You're right sorry, try this at the end, you don't sem to have closed the last if statement.

    IF(AND(Data!BO4="TRUE",Data!H4<7),VLOOKUP(Data!$D4,'Program Codes'!$A:$AH,MATCH(Data!$E4,'Program Codes'!$A$1:$AH$1,0),FALSE)-'Base Price Charges - Credits'!Q4-'Base Price Charges - Credits'!$U$3
    ),0)


    Re: run macro from closed workbook automatically for every 24 hours



    Ensure that cell A1 has a zero in it. This will enable the macro to run automatically when the workbook is opened. The zero changes to a one when it is saved, so when someone opens the saved file the macro doesn't run.


    Change the following to the path of where you want it saved.


    ChDir _
    "C:\Documents and Settings\USER NAME\desktop"

    Re: TO many arguments


    If you Break it down by If formula you have


    IF(D4="",""


    IF(AND(Data!BO4="TRUE",Data!H4=7),VLOOKUP(Data!$D4,'Program Codes'!$A:$AH,MATCH(Data!$E4,'Program Codes'!$A$1:$AH$1,0),FALSE)-'Base Price Charges - Credits'!Q4+'Base Price Charges - Credits'!R4-'Base Price Charges - Credits'!$U$2)


    IF(AND(Data!BO4="TRUE",Data!H4<7),VLOOKUP(Data!$D4,'Program Codes'!$A:$AH,MATCH(Data!$E4,'Program Codes'!$A$1:$AH$1,0),FALSE)-'Base Price Charges - Credits'!Q4-'Base Price Charges - Credits'!$U$3


    The red shows the if TRUE, the blue shows the if FALSE. The 0 doesn't come into play as the formula is complete after the last if FALSE result.

    Re: Only show rows that match a drop down criteria


    In AN3 type =if(or(ak3=$d$1,al3=$d$1,am3=$d$1),1,"") and copy down to row 2000.


    Then write a macro which hides all rows unless they have a 1 in column AN. Attach the macro to a button to activate. You can also have another button which will show all when you don't want the filter anymore.

    Re: use vba to create copies of excel workbook for different users


    One way you can do it is to record a macro, (assuming you can't write it), of what needs to be done for each individual copy and save it as a module within the Master workbook. Within the macro it should also include the saving each copy as it goes along (it's possible to e-mail it out automatically as well if you wish).


    To keep the Master workbook intact you will need to use "ActiveWorkbook.Saved = True" & "Application.Quit" before the end of the macro. The true statement makes excel "think" it has been saved before closing.