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.

Re: Date & Count if unique

Have a look at this one.

Re: Count number of time a time value increases for a particular condition

Couldn't quite follow what you wanted, but I've attached what I think you wanted.

Re: Date & Count if unique

Try the attached sheet. The formulae (in red) should be extended all the way down to cover your data.

Re: Move highest ranked cells to new sheet

Sorry for the delay in getting back to you, Ive been a bit busy.

This should do it for you.

Re: count number of varying sequences

Is the first number included in the possible process? An example sheet with a small sample would help.

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

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

[/TABLE]

Re: Excel order form quantity unit price amount total

Here you go. It's all dynamic so if you change the pricebreaks or pricing it automatically updates.

It using the nested if function.

Re: Correctly Assigning Sales Revenue by Year

Here you go. Changed the thinking process of the formula a bit but it works.

Re: Reports based on data from one cell

Formulae in orange. Just insert rows under each bank and copy the formulae down if you need more rows.

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

Sub Workbook_Open()


   If Sheets("Sheet1").Range("a1").Value > "0" Then
  Exit Sub
    Else
Sheets("Sheet1").Select
    Range("a1").Value = "1"
     'Saves filename as value 'Team Data' plus the current date
     
    Dim newFile As String, fName As String
     ' Don't use "/" in date, invalid syntax
    fName = "Team Data"
     'Change the date format to whatever you'd like, but make sure it's in quotes
    newFile = fName & " " & Format$(Now, "dd-mm-yyyy")
     ' Change directory to suit your PC, including USER NAME
   [COLOR=#ff0000] ChDir _
    "C:\Documents and Settings\USER NAME\desktop"
[/COLOR]    ActiveWorkbook.SaveAs Filename:=newFile
   End If
End Sub

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: Correctly Assigning Sales Revenue by Year

If you can add an attachment I'll have a look, as I'm struggling with where all the figures go.

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: Move highest ranked cells to new sheet

Here you go, I've done it using just formulae. I'm pretty sure it's what you wanted.

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

If you shut down your PC Scheduler will not work.

Once you open the file, scheduled or not the macro will automatically run.

ActiveWorkbook.SaveAs "Team Data" & Format(Now, "dd-mm-yyyy") & ".xls" will save it as requested.

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.

Re: Move highest ranked cells to new sheet

Can you not just use the Large function and then some lookups if you want additional data?