tricks of gurus

[PA HS Teacher]

Thanks for the advice Aladin.

I forgot about index, that is much better than using indirect.

 

[nbrcrunch]

Dang. this thing just up & died. Aladin was one of the those that I was hoping would contribute here. Instead, he just poo-poos the other posters


Come on, throw us bone Aladin! A nice meaty one with lots of marrow.

 

[erik.van.geit]

it's a pitty this one died so early: perhaps the reason was you tried to keep it alive to strongly

something very simple about VBA
when I have some code which has a part I want to "turn on" and "turn off" for testing, it's tedious to comment (out) a bunch of lines
putting them between a "trivial" If ... End If makes the job quickly done

Sub test()
'some codelines
    If True Then 'change to False when needed
    'test codelines to turn on and off
    End If
'some other codelines
End Sub

alternatively you can use
If 1 = 1 Then 'If 1 = 0 Then

voilà
Erik

 

[PaddyD]

re the dying - I'll move to the Lounge

re Aladin 'poo pooing' - I'd rather interpret it as terse advice re implications of using some 'non-standard techniques' that those unfamiliar with them should be aware of

re other odd / undocumented offerings:

1) forgive shameless plug for own post but - get.cell (& by implication other old xl4 marcos):

http://www.mrexcel.com/board2/viewtopic.php?t=21312

2) use of subtotal() to generate an array of filtered items in a list:

http://j-walk.com/ss/excel/eee/eee001.txt

...used, e.g, here:

http://www.mrexcel.com/board2/viewtopic.php?t=42085&highlight=subtotal

...see also the other EE letters for further 'power formula techniques'

3) arrayed use of frequency() to work with 'runs' in data - an example from Domenic:

http://www.mrexcel.com/board2/viewtopic.php?t=244297&highlight=frequency

 

[ExcelChampion]

A common, but not always obvious to novice or intermediate users, use a LOOKUP function or the INDEX/MATCH combination to return a value from a range of values instead of nested IF statements.

As well, use the INDEX/MATCH combination to look across columns, down rows or both. Common knowledge to most on this board, but still a good one for novice and intermediate users.

Not necessarily a function, but formulas entered as NAMES are automatically array entered.

And of course don't forget about the (mostly) undocumented DATEDIF...

 

[barry houdini]

Very interesting links, Paddy.

I particularly like Domenic's FREQUENCY formula, I can just never quite remember how that works when I want to do it.

I've just been playing with some SUBTOTAL/OFFSET constructions myself to return a list of names according to values shown in another column like this:

subtotal_offset.xls
	A	B	C	D
1	name	frequency	list2
2	barry	5	barry
3	phil	1	barry
4	steve	3	barry
5	gavin	0	barry
6			barry
7			phil
8			steve
9			steve
10			steve
11
Sheet1

Formula in C2 copied down

=IF(ROW()-ROW(B$2)< SUM(B$2:B$5),LOOKUP(ROW()-ROW(B$2),SUBTOTAL(9,OFFSET(B$1,,,ROW(B$2:B$5)-ROW(B$2)+1,)),A$2:A$5),"")

I was asked to produce something like this in the past but couldn't do so without a helper column. The SUBTOTAL/OFFSET part is crucial because it allows you an array of ranges, each range one cell greater than the next.

Finally, I like to think that my own "specialist subject" is dates and times and date and time-related formulas. I'm particularly keen on this simple formula which counts the number of Saturdays in a date range (between A1 and B1 inclusive)

=INT((WEEKDAY(A1)+B1-A1)/7)

and which can be adapted to count other days or as a NETWORKDAYS substitute (without the holidays)

 

[ExcelChampion]

Speaking of DATES, again, not undocumented, but still, in my opinion, not used to it's potential:-

=DATE(2006,1,0)

which returns the last day of the previous month specified. I've used this one rather creativly to solve some interesting problems.

 

[barry houdini]

In a similar vein....

=A1-DAY(A1)

a simple way to give the first day of the month of any date in A1.

I first saw this used here by Aladin (who else?)

 

[Richard Schollar]

In a similar vein....

=A1-DAY(A1)

a simple way to give the first day of the month of any date in A1.

I first saw this used here by Aladin (who else?)

Wouldn't that be

=A1-DAY(A1)+1

Barry?

 

[xld]

it's a pitty this one died so early: perhaps the reason was you tried to keep it alive to strongly

something very simple about VBA
when I have some code which has a part I want to "turn on" and "turn off" for testing, it's tedious to comment (out) a bunch of lines
putting them between a "trivial" If ... End If makes the job quickly done

Sub test()
'some codelines
    If True Then 'change to False when needed
    'test codelines to turn on and off
    End If
'some other codelines
End Sub

alternatively you can use
If 1 = 1 Then 'If 1 = 0 Then

voilà
Erik

Surely, you would use conditional compilation?