Posts by 126389

  • Dependent Combo Boxes

    I am using dependent comboboxes because this way the user always sees the dropdown arrow. Isn't there a way to always show that dropdown arrow using lists instead ?
    Thanks

  • Formula to compare columns if the value is same

    Re: Formula to compare columns if the value is same


    I have seen that you were presented with some high-tech solutions to your problem, written in (for me) too difficult code.
    Usually, I try to find a low-tech solution first ... and there is one.
    Have you tried CONDITIONAL FORMATTING combined with some extra COUNTIF columns ?
    Suppose: your 5 lookupcolumns are A2 to E2000. In cell F2 you count all values that are equal to A2

    Code
    =COUNTIF(lookupcolumns,A2)

    ; and drag that code down to F2000. Do the same up to column J (so that you have 5 "COUNTIF" columns that match your 5 lookup columns.
    Then, select cell A2 and in Format-Conditional Formatting, select Formula is and type

    Code
    =F2>1

    ; then goto Format on that same prompt and chooze the format you like, for instance red font. Press OK.
    Now copy the format of cell A2 to all the cells you want evaluated.
    And there you have it.


    Too low-tech ?
    It does make the spreadsheet a little bit heavier because you will add 5 new columns with formulas, but it is easy to comprehend.


    Good luck
    Johan

  • Find Smallest In A List

    I am trying to find the smallest values in a list. that part is easy (use SMALL). But, I want the ref to the cell where that value is in, not the value itself.
    Additional problem occurs when more than one of these values are the same.
    Example: A1 = 1; A2 = 3 ; A3 = 2 ; A4 = 2 ; etc.
    If I want the smallest, I want "A1" as result, for the 2nd smallest I want "A3", for the 3rd smallest I want "A4".
    So you see that a MATCH formula will not work beceause some of the values are the same.


    Any ideas ?


    Many thanks
    Johan

  • If then else if VBA

    Re: If then else in VBA


    Appologies for the late reply but I have been struggling with this issue for the whole weekednd and beyond.
    Root-cause turned out to be an End If that was positioned at the wrong place.
    I also found out that that negation "ElseIf .getstring(8, 49, 4) <> 2400 Then ..." is not required afterall.
    Bottomline is that my program does what it is supposed to do now.


    Many thanks for all your input.
    Johan

  • If then else if VBA

    I am trying to get the below statement work in VBA, but I always get the following error message: "Else without if"
    [vba]
    If Trim(.getstring(8, 49, 4)) = "" Then RSR_OBJECT!ACTDEPTTIME = Null
    Else If .getstring(8, 49, 4) = 2400 Then RSR_OBJECT!ACTDEPTTIME = 23 & ":" & 59
    Else if .getstring(8, 49, 4) <> 2400 Then
    RSR_OBJECT!ACTDEPTTIME = .getstring(8, 49, 2) & ":" & .getstring(8, 51, 2)
    End If
    [/vba]
    Thanks for any help
    Johan

  • [Solved] Lookups in non-sortable list

    Good day, EXCEL QUESTION
    I am basically trying to do a vertical lookup in a non-sortable list (find a name in column A and get the corresponding value from column B).
    Vlookup requires a list that is sorted ascending, but such is not possible in my case.


    Thanks
    Johan

  • [Solved] VBA: Closing files with VBA

    I tried option 2 but I had to write a Close-function for each workbook. I may presume that Wills option makes a bit more sense ; I will try that one later.
    Anyway, it works.
    Greatly appreciated ! Many thanks to all !
    Johan

  • [Solved] VBA: Closing files with VBA

    Hi Will, ea.
    As I said before, the formula works perfectly.
    But now I was trying to see what it does when one of the files is already closed ==&gt;&gt; it gives me an error message, of course (because the files I'm asking to close is already closed).


    Is there some code I can add that checks if the file is open, and if yes then close, if no then disregard. Something like that...


    Thanks in advance
    Johan

  • [Solved] VBA: Closing files with VBA

    Hi all,
    I have lets say 3 Excel workbooks open, and I want to close the 2 that are not ctive by pushing a button.
    I tried writing a simple code like:
    Windows("ARPs.xls").Activate
    ActiveWindow.Close
    Windows("flt_summary.data").Activate
    ActiveWindow.Close
    but I get a prompt asking: "Do you want to save the changes you made ?"
    No, I don't want to save the changes, I just want the workbooks or files to close, so every time I have to click NO.
    Waste of time, I am sure VBA knows a way around that but I don't know it.


    Anyone ???


    While we're at it: is it perhaps possible to close ALL the files by clicking a button in Excel, even the active workbook ?


    Many thanks
    Johan

  • [Solved] Formulas: Date format in Access

    Good day,
    The default setting for Date format in Access is MM/DD/YY.
    But in Europe we usually turn this around = DD/MM/YY format.
    Does anyone know how to force this format in a table in ACCESS without having to use the MEDIUM DATE format ?


    Thank you in advance
    Johan

  • [Solved] Formulas: The 25th of a Month in Access

    Guess what,
    I entered the formula in the Default Value of the Expiration Date in the table ... and it works.


    So I don't have to do it in the query afterall.
    I admit, in the query would be more logic, but technically it works fine in the table.


    Thanks for everything
    Johan

  • [Solved] Formulas: The 25th of a Month in Access

    Hi everyone,
    I have a problem in Access:
    I am preparing a database that shows all kinds of products and the date that these products are sold.
    I want to automatically show an expiration date as well. That date should be the 25th of the current Month + 2.
    Example: product X is sold today 11SEP03 ==&gt;&gt; the expiration date should be 25NOV03.
    Can a function for this be added in the table itself or must that be done in a query ?


    I have been looking through the various available functions but nothing fits.


    I do not know how to create functions myself.


    Many thanks for any hints.


    Johan

  • Formulas for rounding up numbers

    Hi James,
    I checked Access ; it doesn't contain a ROUNDUP function in its expression builder.


    In case that all of the results are between 0 and 1, and every result greater than 0 should read "1", then an IIF function will do (calculated field):
    Roundup: IIf([fraction]&gt;0,1,0)


    Where [fraction] is the name of the field showing your 0.23 result.


    You don't even have to include the field that makes the 230/1000 calculation ; you may make that calculation in the calculated field itself.


    hope this helps
    Johan

  • Excel Rounding

    Hi there,
    try the ROUNDDOWN function.
    Example:
    =ROUNDDOWN(A1,3)
    Where A1 contains 31.354842%


    The result will be 31.3%


    Hope this helps
    Johan

  • Filtering data in Access table

    Hi,


    I am asked to create a report in Access that shows specific data from an Access table.


    Origin Destination Occurrence
    CDG NRT Missed connection
    FRA MXP Damaged
    EWR STN Something else
    CDG FRA DDDD
    CDG AMS DKDKDK
    STN EWR FEUREU
    …


    Above is a small sample of a fake list showing origin, destination and occurrence.
    I would need to produce a report that gives me specific origin-destination pairs.
    For example: I want to ask:
    - everything from CDG to NRT
    - and everything for destination STN
    - and everything with origin FRA
    - etc.


    To do one of these things a simple expression in the query would suffice, but I get stuck with the combination of the various questions that need to be asked.


    The result must show in one and the same report.


    Any ideas are most welcome.
    Johan