Search:

Type: Posts; User: rory; Keyword(s):

Page 1 of 20 1 2 3 4

Search: Search took 0.11 seconds.

  1. Replies
    15
    Views
    298

    Here's a simple example: Public Function...

    Here's a simple example:

    Public Function CompDate(CloseDate As Range, DueDateOffset As Long, _
    ParamArray Criteria() As Variant) As Long

    ...
  2. Replies
    4
    Views
    233

    Macs don't support Access at all, I'm afraid...

    Macs don't support Access at all, I'm afraid (there is no Mac Office equivalent).
  3. Replies
    5
    Views
    140

    You can also: Type 1 in the first cell, then in...

    You can also:
    Type 1 in the first cell, then in the name box (left of the formula bar), type the range you want (eg: A1:A1000) and press Enter, then use the Fill - Series button on the Home tab.
    Or...
  4. Replies
    15
    Views
    298

    Correct. FWIW, a built-in formula should...

    Correct.

    FWIW, a built-in formula should perform better than a UDF but, if you are going to use a UDF, I'd suggest using a ParamArray for the optional arguments so that you can supply as many...
  5. Replies
    15
    Views
    298

    RG's formula is only actually comparing the...

    RG's formula is only actually comparing the closed dates to the date in F8 (specifically the cell in column F in the same row as the formula is entered). If you alter that F8 cell to a value lower...
  6. Replies
    15
    Views
    298

    Try this:...

    Try this:
    =SUMPRODUCT(($J$8:$J$1108="Closed")*($H$8:$H$1108<=$F$8:$F$1108))
  7. Glad to help. This sort of thing helps keep us on...

    Glad to help. This sort of thing helps keep us on our toes, I guess... :)
  8. I blogged it yesterday here:...

    I blogged it yesterday here: http://excelmatters.com/2014/12/10/office-update-breaks-activex-controls/
  9. Thread: VBA Userforms

    by rory
    Replies
    2
    Views
    85

    It's impossible to answer that without seeing the...

    It's impossible to answer that without seeing the workbook and knowing which version of Mac Office you are talking about. 2008 doesn't support VBA - other versions do - but there is not 100%...
  10. Thread: Power pivot 2010

    by rory
    Replies
    3
    Views
    90

    If you have Office 2010 Professional Plus, you...

    If you have Office 2010 Professional Plus, you should be able to use PQ. You can review the system requirements and download it (free) here:...
  11. Replies
    12
    Views
    253

    Also be aware that 2008 doesn't support VBA in...

    Also be aware that 2008 doesn't support VBA in any way shape or form (though you can still use XLM).
  12. Replies
    12
    Views
    253

    It could just be the fact that Excel 2008 is the...

    It could just be the fact that Excel 2008 is the worst version of Excel ever. ;)
  13. You'd have to change the Open command in the...

    You'd have to change the Open command in the registry to look like:
    excel.exe /t "c:\My Folder\book.xltx"

    Also note that if you use File - New - New blank workbook from within Excel, this doesn't...
  14. [SOLVED] In A2:...

    In A2:
    =IF(C2="",0,SUMPRODUCT(($D$25:$P$32=C2)*$C$25:$C$32))
    and copy down.
  15. Thread: Power pivot 2010

    by rory
    Replies
    3
    Views
    90

    Do you have PowerQuery available? It would be...

    Do you have PowerQuery available? It would be much easier to get PQ to merge your tables and then use that as the source for either PowerPivot or just a regular pivot table since you aren't doing...
  16. Replies
    4
    Views
    130

    [SOLVED] And that's why you should provide representative...

    And that's why you should provide representative samples. ;)

    Try this:

    =COUNT(1/FREQUENCY(IF('Data Sheet'!$B$2:$B$128=B$1,IF('Data Sheet'!$D$2:$D$128=$A2,MATCH('Data Sheet'!$A$2:$A$128,'Data...
  17. You're missing my point, I'm afraid. The only...

    You're missing my point, I'm afraid.

    The only thing that will change Excel's default behaviour is setting Cancel = True. That's fine, and your code should do it if you want to prevent the default...
  18. Replies
    4
    Views
    130

    [SOLVED] In B2: =COUNT(1/FREQUENCY(IF('Data...

    In B2:
    =COUNT(1/FREQUENCY(IF('Data Sheet'!$B$2:$B$128=B$1,IF('Data Sheet'!$D$2:$D$128=$A2,'Data Sheet'!$A$2:$A$128)),'Data Sheet'!$A$2:$A$128))
    array-entered with Ctrl+Shift+Enter.
    Then fill down...
  19. FWIW I would not recommend putting Cancel = False...

    FWIW I would not recommend putting Cancel = False without good reason. There may be other event handlers in place that have good reason for Cancelling the default behaviour and doing this could...
  20. Replies
    12
    Views
    242

    RG, This isn't code though. ;) Personally I...

    RG,
    This isn't code though. ;)

    Personally I find & a lot easier to read in a formula.
  21. Replies
    12
    Views
    242

    Out of interest, why would you ever use...

    Out of interest, why would you ever use CONCATENATE in preference to '&'?
  22. Replies
    12
    Views
    242

    You don't actually need to change the name - as...

    You don't actually need to change the name - as with any other 'special' names, you just need to enclose the sheet name in single quotes:
    =INDIRECT("'"&TEXT($B$4,"mmm-yy")&"'!B3")
    Note: your...
  23. Replies
    14
    Views
    737

    You'll have to be more specific, please.

    You'll have to be more specific, please.
  24. Replies
    14
    Views
    737

    Hold the Shift key while opening the workbook.

    Hold the Shift key while opening the workbook.
  25. FWIW, you can also use the TEXT function: ="GGH...

    FWIW, you can also use the TEXT function:
    ="GGH "&TEXT(Max(J:J),"$ #,##0")
    for instance. RG's has the benefit of leaving the result as a number if you need it for other calculations, whereas this...
Results 1 to 25 of 500
Page 1 of 20 1 2 3 4