Search:

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

Page 1 of 20 1

1. Thread: Help with counting dates formula

by rory
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. Thread: Deploy 2013 Access on Mac?

by rory
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. Thread: Quicken the use of fill handle - Excel 2010

by rory
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. Thread: Help with counting dates formula

by rory
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. Thread: Help with counting dates formula

by rory
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. Thread: Help with counting dates formula

by rory
Replies
15
Views
298

Try this:...

Try this:
=SUMPRODUCT((\$J\$8:\$J\$1108="Closed")*(\$H\$8:\$H\$1108<=\$F\$8:\$F\$1108))
7. Thread: Excel 2010 - Button on All Worksheets Stop Working - Excel Update Problem?

by rory
Replies
5
Views
1,228

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. Thread: Excel 2010 - Button on All Worksheets Stop Working - Excel Update Problem?

by rory
Replies
5
Views
1,228

I blogged it yesterday here:...

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

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%...

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. Thread: INDIRECT formula getting corrupted

by rory
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. Thread: INDIRECT formula getting corrupted

by rory
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. Thread: Change format of sheet created using "New" shell command

by rory
Replies
4
Views
134

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. Thread: Must be a formula (array?) that can do this - but I can't figure it out

by rory
Replies
3
Views
130

[SOLVED] In A2:...

In A2:
=IF(C2="",0,SUMPRODUCT((\$D\$25:\$P\$32=C2)*\$C\$25:\$C\$32))
and copy down.

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. Thread: Formula to count unique items

by rory
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. Thread: Solution: How to run an event by double-clicking an excel cell

by rory
Replies
7
Views
241

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. Thread: Formula to count unique items

by rory
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. Thread: Solution: How to run an event by double-clicking an excel cell

by rory
Replies
7
Views
241

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. Thread: Help with indirect formula

by rory
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. Thread: Help with indirect formula

by rory
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. Thread: Help with indirect formula

by rory
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. Thread: How to exit VBA loop

by rory
Replies
14
Views
737

You'll have to be more specific, please.

You'll have to be more specific, please.
24. Thread: How to exit VBA loop

by rory
Replies
14
Views
737

Hold the Shift key while opening the workbook.

Hold the Shift key while opening the workbook.

by rory
Replies
3
Views
108

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