Excel Query

[dbdg]

OT

If you are in New Zealand, how come you finish work the same time as us Brits

[GNEP]

A trixier quick fix which has worked for me in the past is to copy the formula down 6 (edit: might be 5 - it's been a long day!) times by dragging as normal. Then in the column next to it add a random letter or number (anything non-blank) in the top cell only (leaving the others in that column blank). Then highlight all 12 cells (6 rows x 2 cols), and drag down for 6 times as many rows as you need the formula to be copied. The autofilter, filter the second column to be whatever non-blank crud you had in there, and highlight and copy the formulae - this only picks up those showing and not the ones filtered out. Unfilter, and paste somewhere else. Might need to have put the formulae in the "wrong" column to begin with (so when they are pasted they point to the right place)

With variations, this technique can be pretty handy, especially for picking tidying up data that has come in random layouts. Hope it's clear!

[dbdg]

That's a cool upgrade to one of Umfriend's original suggestions, I'll have to remember that for when it isn't worthwhile time wise going the vba route, and for when I get crap spreadsheets from finance.

amazing how much extra excel knowledge I've picked up from you guys.

[Umfriend]

Oh, aside from Help and MURC, there's always the MS newsgroups on Excel funtions, VBA, graphs etc. Although it's sometimes hard to find what it is you're looking for (in which case you'd post a Q), it is a humongeous (sp?) container of information and ideas.

[Ali]

OT

If you are in New Zealand, how come you finish work the same time as us Brits

Because Im working in London now, and havent gotten around to updating my profile