Search:

Type: Posts; User: steve the fish; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.20 seconds.

  1. Replies
    2
    Views
    35

    Re: If statement help

    You can use MOD function eg:

    =IF(D2=G2,"ok",IF(D2>G2,"Supplier MPQ higher",IF(MOD(G2,D2)=0,"MPQ Multiple","Check pack multiple")))
  2. Re: Using VBA to find the address of maximum in a range containing formulae

    You dimension FinSeq as a range so this line:


    FinSeq = TgtRng.Row

    has to fail. Firstly you have to Set objects like ranges and secondly TgtRng.Row isnt a range but an integer.

    You could...
  3. Re: Average With Exclude Text,Blank Cell, Zero Number

    Try

    =AVERAGEIFS(H8:T8,H8:T8,">0")
  4. Re: VBA Code - Closing Statement Wrong Perhaps

    You are missing two End With statements i can see which is a surprise as this is a recorded macro.

    Replace this section:


    Columns("B:B").Select
    Selection.NumberFormat = "m/d/yyyy"
    ...
  5. Re: if date falls on weekend, roll to Monday

    Try this:

    =WORKDAY(A1-1,1)
  6. Re: Place sub category's letters in all items

    First check this formula does what you expect in all of your data. I need to understand your rule.

    =IF(LEFT(G13)="-",LEFT(INDEX(G13:G24,MATCH(1,INDEX(--(RIGHT(F13:F24,5)="Total"),0),0)),4)&G13,G13)
  7. Re: Place sub category's letters in all items

    Where does FRUI and BAKE come from??
  8. Re: How to get 1 vba code to run on all sheets in my workbook when active?

    Right click on ThisWorkbook, view code and use Workbook_SheetBeforeDoubleClick
  9. Re: Help with conditional SUMPRODUCT to also ignore blanks

    The double negative in front of this bit:

    --(U2:U1203=AE3)

    is there to convert a TRUE/FALSE result into a 1 or 0 result. You need the double negative in front of your new TRUE/FALSE test.
  10. Replies
    4
    Views
    98

    Re: Help with Select Case

    No it's an integer. Match will produce the position in the array. 1 for monday 2 for tuesday etc.
  11. Re: If blank cel then dont calc Networdays - Formula Not working

    Because that isnt the syntax for networkdays. It requires at least a start date and an end date.
  12. Re: formula to get the value of the same cell but in different sheets

    You will need to put the sheet name in a cell then do something like:

    =INDIRECT("'"&A1&"'!B2")

    where A1 holds the name of the sheet you are interested in.
  13. Re: Error in index+match multiple criteria

    Try:

    =INDEX('VICEPROCESS NE DN'!$D$2:$S$4236;MATCH(1,(D2='VICEPROCESS NE DN'!$D$2:$D$4236)*(E2='VICEPROCESS NE DN'!$E$2:$E$4236)*(F2='VICEPROCESS NE DN'!$F$2:$F$4236);0);16)
  14. Replies
    4
    Views
    98

    Re: Help with Select Case

    You could also see if this does the same thing:


    dayInt = Application.Match(Me.lDay, Array("monday", "tuesday", "wednesday", "thursday", "friday", "saturday", "sunday"), 0)
    shSum.Range("B" & 2 *...
  15. Replies
    4
    Views
    98

    Re: Help with Select Case

    Do you mean something like this instead? Notice the use of LCase so make all your days lower case.


    Select Case LCase(Me.lDay)
    Case "monday"
    'Do this
    Case "tuesday"
    ...
  16. Re: VBA - Change inputBox date from US to UK standard (mm-dd-yyyy -> dd-mm-yyyy)

    Presuming your local date setting is dd/mm/yyyy then try somthing like this:


    x = InputBox("Enter Date in format DD-MM-YYYY")
    Range("A1") = CDate(x)
  17. Re: How to Re-Write a Very Long Formula

    See if this always produces the same results:

    =IF(K156=0,0,IFERROR(INDEX(AC$2:AT$2,MATCH(1,INDEX(--($J156<=AC156:AT156),0),0)),1000))
  18. Replies
    3
    Views
    131

    Re: Index and Match formula

    Hi. Number 1 would require more info. If a match doesnt find a match then the result is #N/A. You havent given much away in terms of why that may be. Number 2 probablt means the index match match...
  19. Re: VLOOKUP MATCH Not returning all results

    From what you describe i cant see any problem that immediately comes to mind. Certainly cant explain randomness as i cant see that thats possible. In the first row that produces an erroneous result...
  20. Re: runtime error '380 Could not set the rowsource property. Invalid property value

    Do you have a sheet called 'Sheet1'
  21. Re: Excel Date Formula - Production Log

    Yes the way ive written it here 'holidays' is a named range but you could just replace it with cell references. Once we reach november the formula will adjust to look for november dates. This...
  22. Re: Excel Date Formula - Production Log

    Always good to give an example. Worth many words. Try this:

    =MAX(0,NETWORKDAYS(MAX(A3,EOMONTH(TODAY(),-1)+1),MIN(B3,EOMONTH(TODAY(),0)),holidays))
  23. Re: Excel Date Formula - Production Log

    Hi. If im understanding you want the number of days that appear between those two dates that are in the current month but starting from today? If so try:
    ...
  24. Re: how to use a cell to reference a file?

    As i was trying to say in post number 12 if you type anything into a cell with a leading ' excel will use that ' to think you mean this is text and ignore it. Therefore your indirect will fail as it...
  25. Re: how to use a cell to reference a file?

    You will be losing the initial ' if you type that as the first character in a cell.
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4