Search:

Type: Posts; User: Jon von der Heyden; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.39 seconds.

  1. Replies
    6
    Views
    308

    Re: compare columns

    or:


    Public Sub IloveVBA()
    Dim rng1 As Excel.Range
    Dim rng2 As Excel.Range
    Dim rngCell As Excel.Range
    Dim strJustynasFormula As...
  2. Re: how to change colour of cell if anything is entered in textbox

    Howdy

    Remove the = ""


    If Len(TextBox14.value) = "" Then
  3. Re: how to change colour of cell if anything is entered in textbox

    You could check that the length of the text in the TextBox is not 0:



    If Len(TextBox14.Value) Then
  4. Re: DATEVALUE in Excel, handy tips?

    Hi

    What version of Excel are you using?

    Assuming I understand your question, both WEEKNUM and WEEKDAY functions support unnatural selections, although I cannot recall when this came in to...
  5. Re: Parsing a formula: qualify sheet for all range references

    Wonderful - thank you Pedro! Very cunning workaround and it seems to be working a treat!
  6. Re: Parsing a formula: qualify sheet for all range references

    Hi Marcelo

    Thanks though for having a crack. Still gives food for thought because Worksheet.Evaluate could indeed have offered some for of workaround.
  7. Parsing a formula: qualify sheet for all range references

    Hi All

    I have a very large workbook with a large volume of validation rules across multiple sheets. The validation uses custom hence standard formulas that evaluate to either TRUE/FALSE.

    I...
  8. Replies
    11
    Views
    397

    Re: Rounding to the nearest 0.25

    I was also working to that assumption. Adjusting my formula:

    =CEILING(A2-"00:07:30","00:15")*24
  9. Replies
    11
    Views
    397

    Re: Rounding to the nearest 0.25

    Example results:

    Excel 2010
    Row\Col
    A
    B

    1Time SerialRound Up .25
  10. Replies
    11
    Views
    397

    Re: Rounding to the nearest 0.25

    If your cell has time serial values, then:

    =CEILING(A1,"00:15")*24
  11. Replies
    11
    Views
    397

    Re: Rounding to the nearest 0.25

    Hi

    To round up you to a significance you can also use CEILING. E.g =CEILING(2.55,0.25)

    You can also roundup time values (e.g. 02:55) to a significance of 15 minutes, e.g....
  12. Re: Rounding to nearest specified day

    Simplify:

    =CEILING(A1-4,7)+2
  13. Re: Rounding to nearest specified day

    Assuming I understand the brief, try:

    =IF(WEEKDAY(A1,1)<5,FLOOR(A1,7),CEILING(A1,7))+2
  14. Re: Returning MAX value based on multiple criteria.

    Hi

    In C2 copied down:

    =IF(A2*B2,MAX(A2:B2),"")
  15. Replies
    11
    Views
    443

    Re: List specific number

    Glad it's working for you. Another VBA approach:


    Public Sub NotAFormula()
    Dim varVals As Variant
    Dim lngItem As Long
    Dim lngCount As Long
    ...
  16. Replies
    11
    Views
    443

    Re: List specific number

    Hi

    Using the second formula that I suggested.

    Excel 2010
    Row\Col
    A
    B

    1NumsResults
  17. Re: Type Mismatch trying to find cell value

    Hi

    You are using Set YourVariable...

    Set is for Objects but you are after the Value property. Either lose the Set or replace with Let.
  18. Replies
    11
    Views
    443

    Re: List specific number

    Hi

    If your values are in A1:A100:

    =INDEX(A1:A100,SMALL(IF(RIGHT(A1:A100,1)="8",ROW(A1:A100)),1),1)

    And confirm with control + shift + enter

    Change the 1 to the nth occurrence that you...
  19. Replies
    2
    Views
    133

    Re: Summing up TIME - 2 Tables

    Or using Excel formulas

    Excel 2010
    Row\Col
    A
    B
    C
    D
    E
    F
  20. Re: AverageifS on a range containing Div/Value/Error...

    You cannot use arrays (which one could use to test for error type) in AVERAGEIFS so I believe you need to proceed with the ">0" condition:

    =AVERAGEIFS(A1:A5,A1:A5,">0",B1:B5,"Correct",C1:C5,"Yes")
  21. Re: Multiple Drop Down Selection Lists

    Hi Jason

    Contextures offers a nice tutorial here: https://www.contextures.com/xlDataVal02.html
  22. Replies
    6
    Views
    250

    Re: Sumproduct

    Although you can simplify if you use avoid using whole column and if your data is in a contiguous range.

    =SUMPRODUCT(B1:B100-A1:A100)+SUMPRODUCT(--(A1:A100=B1:B100))

    The whole column approach...
  23. Replies
    6
    Views
    250

    Re: Sumproduct

    Try:

    =SUMPRODUCT(B:B-A:A)+SUMPRODUCT(--(A:A=B:B),--(A:A&B:B<>""))
  24. Replies
    6
    Views
    250

    Re: Sumproduct

    Hi

    What does the first formula give you? I think it should work.
  25. Replies
    4
    Views
    200

    Re: Formula Issue

    Try:

    =IF(LEN(C12),TEXT(C12,"# ?/?")&" ","")&IF(LEN(D12),TEXT(D12,"dd/mm/yyyy")&"x ","")&E12&" "&F12
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4